SQL Copy Row for a list, change one column value

Question

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?

Answer 1

INSERT
INTO    mytable
SELECT  othertable.tabid, mytable.othercolumn
FROM    mytable
CROSS JOIN
        othertable
WHERE   othertable.parentid = 1

This implies there is only one record with parentid = 1 in othertable (otherwise your subselect would fail)

Answer 2

Do you have access to using SQL Server CLR? You could write your code in C# or VB using the usual loops and get this done quite handily.

In the absence of CLR, you could compose a giant SQL string in just about any coding language, and send it to the database. That way, you could use the usual looping (for loop, do loop, foreach).

Answer 3

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

Answer 4

I'll note that using the GO statement with your query, you can do things like GO 2000 and your statement will be executed 2000 times. You can insert 2000 rows that way easily. That might at least help you test and plan your solution.

Example:

CREATE TABLE #TableExample(thisid int IDENTITY(1,1) , thiscol varchar(1))

INSERT INTO #TableExample ( thiscol ) VALUES  ('x')
GO 2000 -- INSERT Statement will execute 2000 times.


DROP TABLE #TableExample

Can you explain why you need to duplicate the rows and what they will be used for? If you share those assumptions, the feedback you get might lead to changing those assumptions and ultimately lead to a better solution.