SQL Copy Row for a list, change one column value

insertsqltsql

I need to duplicate a row a couple thousand times. I need to change one column from the copied row based on a list of ids.
Psuedo-code:

INSERT INTO MyTable (TabID, Other Columns)
VALUES (TabID = (SELECT TabID FROM OtherTable WHERE ParentID = 1), Other Columns)

Is this doable?

Best Solution

How do you want to change Tab1? If you wanted to increment it, for sake of argument, you could do this:

declare int @i
set @i = 0
while (@i < 1000)
begin
    INSERT INTO MyTable (TabID, col1, col2)
    SELECT TabID+1, col1, col2
    FROM OtherTable
    WHERE ParentID = 1 -- assuming only one row with that ID

    set @i = @i+1
end

A cleaner, neater way is to create a numbers table (code below untested):

DECLARE @numbers TABLE (n int)
declare int @i
set @i = 0
while (@i < 1000)
begin
    INSERT INTO @numbers (n) VALUES (i)
    set @i = @i+1
end

INSERT INTO MyTable (TabID, col1, col2)
SELECT TabID+1, col1, col2
FROM OtherTable
CROSS JOIN @numbers n
WHERE ParentID = 1 -- assuming only one row with that ID
Related Question