Insert more than 200 rows at once?

[ivamluz]

Just saw here that it's possible to insert multiple rows by using something like:

INSERT INTO MyTable (Column1, Column2)
SELECT Value1, Value2
UNION ALL
SELECT Value3, Value4
UNION ALL
...

It seems to work fine, except when we have more than 200 rows. Then we get:

Response message: nl.topicus.jdbc.exception.CloudSpannerSQLException: INVALID_ARGUMENT: Number of UNION operations exceeds the maximum allowed limit of 200.

Is this a hard limit on Spanner or on the lib? If on the lib, is this configurable?

[olavloite]

This is a hard limit in Cloud Spanner and not configurable: https://cloud.google.com/spanner/quotas#query_limits

What you could do, if you are inserting the rows using an application you have written yourself, is to use the JDBC batching functionality. By using either Statement#addBatch(String) or PreparedStatement#addBatch(), you can batch a large number of insert statements together and send them as one batch to Spanner by calling Statement#executeBatch().

[ivamluz]

Thank you very much, @olavloite .