How to do a batch insert version of auto increment?

Question

I have a temporary table that has columns id and value:

| id | value |

And I have a table that aggregates data with columns g_id and value:

| g_id | value |

The id in the temporary table is locally ordered by id, here's 2 examples:

temp_table 1

| id | value  |
+----+--------+
| 1  | first  |
| 2  | second |
| 3  | third  |

temp_table 2

| id | value |
+----+-------+
| 2  | alpha |
| 3  | beta  |
| 4  | gamma |

I want to insert all the rows from the temp table into the global table, while having the g_id ordered globally. If I insert temp table 1 before temp table 2, it should be like this:

global_table

| group_id | value  |
+----------+--------+
| 1        | first  |
| 2        | second |
| 3        | third  |
| 4        | alpha  |
| 5        | beta   |
| 6        | gamma  |

For my purposes, it is ok if there are jumps between consecutive numbers, and if 2 inserts are done at the same time, it can be interleaved. The requirement is basically that the id columns are always increasing.

e.g. Let's say I have 2 sets of queries run at the same time, and the group_id in global_table is auto incremented:

Query 1:

INSERT INTO global_table (value) VALUES (first)
INSERT INTO global_table (value) VALUES (second)
INSERT INTO global_table (value) VALUES (third)

Query 2:

INSERT INTO global_table (value) VALUES (alpha)
INSERT INTO global_table (value) VALUES (beta)
INSERT INTO global_table (value) VALUES (gamma)

I can get something like this:

global_table

| group_id | value  |
+----------+--------+
| 1        | first  |
| 3        | alpha  |
| 4        | second |
| 5        | third  |
| 6        | beta   |
| 7        | gamma  |

How do I achieve something like this with inserting from a table? Like with

INSERT INTO global_table (value)
    SELECT t.value
    FROM temp_table t

Unfortunately, this may not result in a incrementing id all the time.

If global_table.id is auto incremented, your insert into select will work. — Schwern, Feb 22 '21 at 08:47
To be specific, I'm using SQL Server, but it will be very nice to have a general answer that will work for any database product, rather than using a feature specific to a specific vendor. — Eric, Feb 22 '21 at 10:39
@Eric what is the actual problem? `this may not result in a incrementing id all the time.` no, it will result in incementing IDs *every* time. You don't have to do anything for any database, any vendor. If the columns are auto-generated, they will be generated by the database for every new row. — Panagiotis Kanavos, Feb 22 '21 at 10:57
@Eric do you mean *consecutive* instead of incrementing perhaps? Why, does the key have some business value? In any other case, there's no problem if the IDs have gaps as long as new values are always larger than existing ones — Panagiotis Kanavos, Feb 22 '21 at 10:58
BTW 3 separate INSERTs isn't a batch operation, it's 3 independent INSERTs. To insert 3 rows at once you can use [table value constructor syntax](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15), eg `INSERT INTO ... VALUES ( (first),(second),(third) )` — Panagiotis Kanavos, Feb 22 '21 at 11:00
It needs to be incrementing with respect to the ids in the temp_table, If it inserts `first, third, second`, the ids in the global_table will be wrong — Eric, Feb 23 '21 at 03:32

Vladimir Baranov · Accepted Answer · 2021-02-22T10:53:46.403

The requirement is basically that the id columns are always increasing.

If I understood you correctly, you should use ORDER BY in your INSERT statement.

INSERT INTO global_table (value)
SELECT t.value
FROM temp_table t
ORDER BY t.id;

In SQL Server, if you include ORDER BY t.id, it will guarantee that the new IDs generated in the global_table table will be in the specified order.

I don't know about other databases, but SQL Server IDENTITY has such guarantee.

Using your sample data, it is guaranteed that the group_id generated for value second will be greater than the value generated for the value first. And group_id for value third will be greater than for value second.

They may be not consecutive, but if you specify ORDER BY, their relative order will be preserved.

Same for the second table, and even if you run two INSERT statements at the same time. Generated group_ids may interleave between two tables, but relative order within each statement will be guaranteed.

See my answer for a similar question with more technical details and references.

How to do a batch insert version of auto increment?

1 Answers1