In procedural SQL, I could write a loop that used an incrementing variable to build data rows and insert them into a table one by one. Is there a way to get the same effect using the regular SQL syntax supported by CockroachDB? I don't want to have to programatically build a
INSERT INTO ... VALUES ( ... )
statement, or run many inserts in a loop outside of database code.
There's some nice syntax for this! You can write
INSERT INTO tablename(<column list>) SELECT <expr> FROM generate_series(1,<n>) i;
generate_series(start, end) i can be selected from as though it were a table with a single column, i, with rows valued from start to end. You can use this to build a tuple that lines up with the columns you're populating, and insert all the results at once. For example,
INSERT INTO foo(id, name, data) SELECT i, 'name'||i, random() FROM generate_series(1,1000) i;
Will insert 1000 rows with ids 1-1000, names that look like "name632", and random floats in the data column.