INSERT INTO statement that copies rows and auto-increments non-identity key ID column
- by AmoebaMan17
Given a table that has three columns
ID (Primary Key, not-autoincrementing)
GroupID
SomeValue
I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that has one GroupID into a new GroupID.
Example beginning table:
ID | GroupID | SomeValue
------------------------
1 | 1 | a
2 | 1 | b
Goal after I run a simple INSERT INTO statement:
ID | GroupID | SomeValue
------------------------
1 | 1 | a
2 | 1 | b
3 | 2 | a
4 | 2 | b
I thought I could do something like:
INSERT INTO MyTable
( [ID]
,[GroupID]
,[SomeValue]
)
(
SELECT (SELECT MAX(ID) + 1 FROM MyTable)
,@NewGroupID
,[SomeValue]
FROM MyTable
WHERE ID = @OriginalGroupID
)
This causes a PrimaryKey violation since it will end up reusing the same Max(ID)+1 value multiple times as it seems.
Is my only recourse to a bunch of INSERT statements in a T-SQL WHILE statement that has an incrementing Counter value?
I also don't have the option of turning the ID into an auto-incrementing Identity column since that would breaking code I don't have source for.
