SQL Copy Row for a list, change one column value

I need to duplicate a row a couple thousand times. I need to change one colum开发者_StackOverflow社区n 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?

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

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)

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).

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.

I had to solve a similar problem with my 2 tables: User and UserCustomSetting. I wanted every user to inherit the settings of the "Default" user record.

I solved with a simple CROSS JOIN. This is my T-SQL query

DECLARE @Username VARCHAR(100) = 'DefaultUser';

--get default UserID
DECLARE @UserID INT = (SELECT UserID FROM [User] WHERE [User].Username = @Username)

-- delete previous settings (except default user)
DELETE FROM UserCustomSetting WHERE UserCustomSetting.UserID <> @UserID

-- insert new settings for all users, copy them from the default user
INSERT INTO UserCustomSetting                               
SELECT u.UserID, ucs.SettingKey, ucs.Value
FROM [User] AS u
CROSS JOIN UserCustomSetting AS ucs 
WHERE ucs.UserID = @UserID
AND u.Username <> @Username

Cross joins can be difficult to get your head around. I had a similar problem on a permissions table. I wanted to copy user 1's permissions to a new user (10):

UserID      ItemID     
1           CBE17 
1           184AB
1           459FA 
1           3856D
1           A261D

You could add more columns if needed.

INSERT INTO UserPermissions (UserID, ItemID)
   SELECT 
      12, 
      ItemID 
   FROM UserPermissions WHERE RoleID = 1