INSERT INTO statement that copies rows and auto-increments non-identity key ID column

Question

Given a table that has three columns

1. ID (Primary Key, not-autoincrementing)
2. GroupID
3. 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.

Answer 1

WITH    q AS
        (
        SELECT  *,
                (
                SELECT  MAX(id)
                FROM    mytable
                ) + ROW_NUMBER() OVER () AS nid
        FROM    mytable
        WHERE   groupID = 1
        )
INSERT
INTO    mytable (id, groupid, somevalue)
SELECT  nid, 2, somevalue
FROM    q

Answer 2

Use this:

INSERT INTO MyTable
(       [ID]
       ,[GroupID]
       ,[SomeValue]
)    
 SELECT ROW_NUMBER() OVER() + X.MaxID 
       ,@NewGroupID
       ,[SomeValue]
 FROM MyTable
 CROSS JOIN (SELECT MAX(ID) AS MaxID FROM MyTable) X 
 WHERE GroupID = @OriginalGroupID

Answer 3

Instead of + 1, add the row number. I also fixed the error in your WHERE clause (should be GroupID =, not ID =):

INSERT INTO MyTable
(       [ID]
       ,[GroupID]
       ,[SomeValue]
)
(
    SELECT
       (SELECT MAX(ID) FROM MyTable) + ROW_NUMBER() OVER (ORDER BY GroupId),
       @NewGroupID,
       [SomeValue]
    FROM MyTable
    WHERE GroupID = @OriginalGroupID
)