SQL Server insert statement to auto increment

Question

I have a SQL insert statement like the following:

insert into mytable(ID,Keyindex,KeyValue) 
select Id,1,GenreID 
from mytable2

Which should populate my table with data like:

id                                      GenreID  ColumnB
0006342c-47bc-436a-a23a-3b40360d9a30    16       1
0006342c-47bc-436a-a23a-3b40360d9a30    19       1
00109775-f0f8-463e-8134-f842aac8b5df    12       1
001211e3-9bf8-45ad-8297-7a0a94aaf06e    13       1
0025218a-9624-4f5e-86cc-f1cfe862cd2a    16       1 
0025218a-9624-4f5e-86cc-f1cfe862cd2a    11       1
0025218a-9624-4f5e-86cc-f1cfe862cd2a    15       1

The problem is ID,GenreId and ColumnB are primary keys and I am currently inserting a constand value "1" which results in a primary key violation.

How do I insert into the table so that ColumnB gets populated with a value incrementaly if the IDs are the same.

For example:

id                                      GenreID  ColumnB
0006342c-47bc-436a-a23a-3b40360d9a30    16       1 
0006342c-47bc-436a-a23a-3b40360d9a30    19       2 
00109775-f0f8-463e-8134-f842aac8b5df    12       1
001211e3-9bf8-45ad-8297-7a0a94aaf06e    13       1
0025218a-9624-4f5e-86cc-f1cfe862cd2a    16       1
0025218a-9624-4f5e-86cc-f1cfe862cd2a    16       2
0025218a-9624-4f5e-86cc-f1cfe862cd2a    16       3

The column names in your first part are out of synch with those shown in the second part. — Martin Smith, Feb 24 '12 at 20:58
So you just want ColumnB to increment if the combination of GenreID and ColumnB will not be unique? Sounds like you'll need a trigger. — davidethell, Feb 24 '12 at 21:00

score 1 · Answer 1 · answered Feb 24 '12 at 22:41

Your question is a little confusing. In the "for example" section it looks like the genreID for 0025218a-9624-4f5e-86cc-f1cfe862cd2a should be 16,17,19 instead of 16,16,16.

Assuming I understand you correct, though, you can use ROW_NUMBER() OVER partitioned by the id. Something like the following should work :

insert into mytable(ID,Keyindex,KeyValue) 
select   id, GenreID , ROW_NUMBER() OVER (Partition by id order by id) as ColumnB
from     mytable2
order by id, 
         genereid

Note: you didn't specify version, but this will work in sql 2005+

score 0 · Answer 2 · answered Feb 24 '12 at 21:00

0

A dirty hack would be to use id - someNumber or id + someNumber instead of just 1.

What can be accepted if this is a "fire and forget" one time operation.

If you have to check the uniqueness, then a trigger can be the solution.

answered Feb 24 '12 at 21:00

Ricardo Souza

16,030
6
37
69

score 0 · Answer 3 · answered Feb 24 '12 at 21:03

0

You could try the ON DUPLICATE option:

insert into mytable(ID,Keyindex,KeyValue) 
select Id,1,GenreID 
from mytable2
ON DUPLICATE KEY UPDATE Keyindex = Keyindex+1;

answered Feb 24 '12 at 21:03

davidethell

11,708
6
43
63

Note this is untested. Just a quick thought. Modify as needed. – davidethell Feb 24 '12 at 21:04
If you're not using MySQL, check out this link: http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update – sazh Feb 24 '12 at 21:07
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'ON'. – Milligran Feb 24 '12 at 21:39
The ON DUPLICATE syntax won't work for SQL server. You'll need a BEFORE INSERT trigger. – davidethell Feb 24 '12 at 22:05

SQL Server insert statement to auto increment

3 Answers3