How to make a copy of a relational record in the same tables?

Question

I have three related tables - Users have Groups, and Groups have Items:

Id   UserName
1    Joe
2    Fred

Id   GroupName    UserId
1    TestGr1      1
2    TestGr2      1

Id   ItemName     GroupId   UserId
1    Item1        1         1
2    Item2        1         1
3    Item3        2         1
4    Item4        2         1

I want to give Fred everything that Joe has, i.e. copy all the related data in Groups and Items.

I have copied the Groups:

insert into Groups
select [GroupName], 2
from Groups
where UserId = 1

How do I proceed with items?

UPDATE
Here's what I want in the end:

Id   UserName
1    Joe
2    Fred

Id   GroupName    UserId
1    TestGr1      1
2    TestGr2      1
3    TestGr1      2
4    TestGr2      2

Id   ItemName     GroupId   UserId
1    Item1        1         1
2    Item2        1         1
3    Item3        2         1
4    Item4        2         1
5    Item1        3         2
6    Item2        3         2
7    Item3        4         2
8    Item4        4         2

@njk: Item *is* a separate table. It has UserId for convenience. — z-boss, Feb 11 '13 at 17:24
is the problem that when you inserted into Groups, it created two new IDs, say 3 and 4, and you want these new IDs in item instead of Joe's group IDs? — Beth, Feb 11 '13 at 17:28
Which version of SQL Server? Also, are the UserName, GroupName and ItemName columns unique (your insert would make it seem like GroupName at least is not unique)? — Tim Lehner, Feb 11 '13 at 17:35
As @MikaelEriksson has previously said, [If you are on SQL Server 2008 you should have a look at this](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id). — Tim Lehner, Feb 11 '13 at 17:41

score 2 · Accepted Answer · edited Feb 11 '13 at 17:56

2

I think this might work for you....

--create variables to store the new users
declare @user_from int,
    @user_to int

set @user_from = 1
set @user_to = 2

--insert the new groups
insert into Groups (GroupName, UserId)
select GroupName, @user_to
from Groups
where UserId = @user_from

--insert the item values matching on the group names but using the new groups
insert into Items (ItemName,GroupId,UserId)
select i.ItemName, g2.Id, @user_to
from Items i
inner join Groups g on g.Id = i.GroupId
inner join Groups g2 on g2.GroupName = g.GroupName
                 and g2.UserId = @user_to
where i.UserId = @user_from

edited Feb 11 '13 at 17:56

z-boss

17,111
12
49
81

answered Feb 11 '13 at 17:44

darin

498
3
6

Exactly! Thank you. I fixed it a bit after testing. It's a good thing I have unique Group Names. – z-boss Feb 11 '13 at 17:57
Note that running this twice (or simply having an existing Group be common to both users) would create more Items than you probably want. – Tim Lehner Feb 11 '13 at 18:04

score 1 · Answer 2 · answered Feb 11 '13 at 17:56

1

Here is another version

--insert items
insert into Groups      
select [GroupName], 2
from Groups
where UserId = 1

--insert groups
DECLARE @tblGroups Table(
NewGroupId int not null, 
OldGroupId int not null)
insert into @tblGroups
select ng.id, og.id
from Groups ng, groups og
where ng.groupname = og.groupname 
AND UserId = 1

insert into items
select i.itemname, t.newgroupid, 2
from items i, @tblgroups t
where t.oldgroupid = i.groupid 
and  i.userid = 1

answered Feb 11 '13 at 17:56

Sunny

4,765
5
37
72

I think Darin's answer is good since table variable is redundant here. – Sunny Feb 11 '13 at 17:59
thank you for this on too. That's the way I initially tried but stuck. – z-boss Feb 11 '13 at 18:01

Matt · Answer 3 · 2013-02-12T15:49:56.070

I've been meaning to get some practice with the merge statement and the output statement together. Replace all the temp table information with your table names. I abstracted the @OldUserID and @NewUserID out so that this could easily be written as a stored procedure.

Cheers!

Declarations:

DECLARE @TempUsers TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),UserName VARCHAR(25));
DECLARE @TempGroups TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1), GroupName VARCHAR(25),UserID INT);
DECLARE @TempItems TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),ItemName VARCHAR(25),GroupID INT, UserID INT);

INSERT INTO @TempUsers SELECT 'Joe' UNION SELECT 'Fred';
INSERT INTO @TempGroups SELECT 'TestGr1',1 UNION SELECT 'TestGr2',1;
INSERT INTO @TempItems SELECT 'Item1',1,1 UNION SELECT 'Item2',1,1;
INSERT INTO @TempItems SELECT 'Item3',2,1 UNION SELECT 'Item4',2,1;

SELECT 'OldUsers' AS TableName,* FROM @TempUsers
SELECT 'OldGroups' AS TableName,* FROM @TempGroups
SELECT 'OldItems' AS TableName,* FROM @TempItems

DECLARE @TempCorrelationTable TABLE (OldID INT, [NewID] INT);
DECLARE @OldUserID INT = 1;
DECLARE @NewUserID INT = 2;

Solution:

MERGE INTO @TempGroups AS T
USING (SELECT ID,GroupName FROM @TempGroups) AS S
ON 1=2
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (GroupName,@NewUserID)
OUTPUT S.ID, INSERTED.ID INTO @TempCorrelationTable;

MERGE INTO @TempItems AS T
USING (SELECT ItemName,[NewID] FROM @TempItems AS TI INNER JOIN @TempCorrelationTable AS TC ON (TI.GroupID = TC.OldID)) AS S
ON 1=2
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (ItemName,[NewID],@NewUserID);

Results:

SELECT 'NewUsers' AS TableName,* FROM @TempUsers
SELECT 'NewGroups' AS TableName,* FROM @TempGroups
SELECT 'NewItems' AS TableName,* FROM @TempItems

How to make a copy of a relational record in the same tables?

3 Answers3