Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1
 
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

share|improve this question

3 Answers

up vote 3 down vote accepted 
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.

share|improve this answer
 
This worked, thanks for the help. –  weewa Dec 12 '12 at 19:17
 
@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 Sep 4 at 15:39
1  
@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. –  RichardTheKiwi Sep 4 at 17:52
 
You learn something new every day - Thanks! –  SelectDistinct Sep 5 at 8:21
up vote 2 down vote

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.

share|improve this answer
 
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. –  weewa Dec 12 '12 at 19:21
1  
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 –  AlejoBrz Jun 24 at 17:03
up vote 0 down vote

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
share|improve this answer
 
Does that mean change the column design? I don't have access to do that. Primary_Key has to be an INT –  weewa Dec 12 '12 at 18:08
1  
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. –  Kaf Dec 12 '12 at 18:15
 
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. –  weewa Dec 12 '12 at 18:17
 
Then you won't be able to do a batch insert. Insert 1 at a time. –  Kaf Dec 12 '12 at 18:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.