Insert multiple rows with incremental primary key sql

insert, primary-key, sql, sql-server-2008

Solution

INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY)
SELECT COLUMN1,
       (SELECT COALESCE(MAX(PRIMARY_KEY),0)
       FROM TABLE1) + row_number() over (order by 1/0)
FROM TABLE 2

For this statement alone, the IDs will be sequential, e.g. if `Max(Primary Key)` is 99 and it is inserting 4 records, they will be 100, 101, 102, 103. It's very prone to constraint violations if multiple processes are inserting at the same time, but that's not to say it is any worse than what you have with a single record anyway using `MAX()` which is inherently unsafe.

Problem

``` INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY) SELECT COLUMN1, (SELECT COALESCE(MAX(PRIMARY_KEY), 0) FROM TABLE1) + 1 FROM TABLE2 ``` error: Violation of Primary Key constraint. Cannot insert duplicate key in object. How do I make the primary key increment after the first row? I would like to be able to add a list of items to this table at the same time instead of inserting them RBAR. Thanks for the help

Original source