Insert multiple rows with incremental primary key sql

Question

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

Answer 1

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.

Answer 2

You can try this:

DECLARE @CurrentPK INT
SELECT @CurrentPK(MAX(PRIMARY_KEY)

SELECT column1, ROW_NUMBER() OVER (ORDER BY column1) AS 'RowNumber'
INTO #temp
FROM Table2

INSERT INTO TABLE1
(COLUMN1, PRIMARY_KEY)
SELECT COLUMN1,@CurrentPK+RowNumber 
FROM #temp

Of course to prevent race conditions, you should put this in a transaction and explicitly lock out other inserts happening at the same time. Your best bet is a stored proc with try6 catch blocks as well as transaction processing.

I want you to understand that it is not an option to avoid transactions in this case. If you do not specifically use transactions, you will have times when two tprocessses try to use the same id number. In fact, that is why the method of getting the last id number not recommednd as it is all too easy to create database problems using it. I know you are stuck with this, but at least learn to never use this sort of short-sighted antipattern in the future.