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

RichardTheKiwi · Accepted Answer · 2012-12-12 18:51:18Z

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.

@richardthekiwi I get that the MAX(Primary_key) finds the exisiting max value and then you are adding 1,2,3,4 to this for each of the respective examples. Could you explain to me what the over (order by 1/0) is doing though?
@SelectDistinct row_number() must be paired with an order by. Lacking something good to order by, the expression "1/0" resolves to a no-op (something undefined) so row_number() is free to assign numbers to the SELECT output in any order.

HLGEM · Accepted Answer · 2012-12-12 18:59:44Z

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.

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

Indi · Accepted Answer · 2012-12-12 18:05:46Z

-1

You don't have to check for max key and increment 1 by 1. Make it an IDENTITY(1,1) NOT NULL column and Server will look after it. Then use;

INSERT INTO TABLE1 (COLUMN1) SELECT COLUMN1 FROM TABLE 2

answered Dec 12, 2012 at 18:05

Indi

33.9k7 gold badges61 silver badges80 bronze badges

4 Comments

weewa Over a year ago

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

Indi Over a year ago

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 Over a year ago

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.

Indi Over a year ago

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

Collectives™ on Stack Overflow

Insert multiple rows with incremental primary key sql

3 Answers 3

2 Comments

2 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

4 Comments

Linked

Related