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

Solution 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.

Solution 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.

Comments

[weewa]

Does that mean change the column design? I don't have access to do that. Primary_Key has to be an INT

[Kaf]

Primary_Key can be an INT no problem at all. Make it an IDENTITY(1,1) as well. Yes you have to change the column design. Your method is not recommended.

[weewa]

The table I am writing to is for a program we use and I don't have the option to change the design. Gotta make due with how its setup, sadly.

[Kaf]

Then you won't be able to do a batch insert. Insert 1 at a time.

[weewa]

Do you have an article I can read on this to learn more? I am writing this data to a program we use/bought and their support said to use max(). So I am very dumb in this area but would like to educate myself more on the right way.

[Alejandro B.]

No need for a temp table, you can join it as a derived table on the SELECT FROM Table2. But this way is clearer though