Join two parent sets of child tables

Question

I need to get data in two parent> sets of child tables, merged / merged into a third parent> child table.

The tables look like this:

The only difference in the three sets of tables is that TableC has a TableType column to recognize the difference between the TableA record and the TableB record.

My first thought was to use a cursor. Here's the code to create the table structure, insert some records, and then combine the data together. It works very well, sooooo ....

 --Create the tables CREATE TABLE TableA ( ID int not null identity primary key, Name VARCHAR(30) ); CREATE TABLE TableAChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_A FOREIGN KEY (Parent) REFERENCES TableA(ID) ); CREATE TABLE TableB ( ID int not null identity primary key, Name VARCHAR(30) ); CREATE TABLE TableBChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_B FOREIGN KEY (Parent) REFERENCES TableB(ID) ); CREATE TABLE TableC ( ID int not null identity primary key, TableType VARCHAR(1), Name VARCHAR(30) ); CREATE TABLE TableCChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_C FOREIGN KEY (Parent) REFERENCES TableC(ID) ); -- Insert some test records.. INSERT INTO TableA (Name) Values ('A1') INSERT INTO TableAChild (Name, Parent) VALUES ('A1Child', SCOPE_IDENTITY()) INSERT INTO TableB (Name) Values ('B1') INSERT INTO TableBChild (Name, Parent) VALUES ('B1Child', SCOPE_IDENTITY()) -- Needed throughout.. DECLARE @ID INT -- Merge TableA and TableAChild into TableC and TableCChild DECLARE TableACursor CURSOR -- Get the primary key from TableA FOR SELECT ID FROM TableA OPEN TableACursor FETCH NEXT FROM TableACursor INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN -- INSERT INTO SELECT the parent record into TableC, being sure to specify a TableType INSERT INTO TableC (Name, TableType) SELECT Name, 'A' FROM TableA WHERE ID = @ID -- INSERT INTO SELECT the child record into TableCChild using the parent ID of the last row inserted (SCOPE_IDENTITY()) -- and the current record from the cursor (@ID). INSERT INTO TableCChild(Name, Parent) SELECT Name, SCOPE_IDENTITY() FROM TableAChild WHERE Parent = @ID FETCH NEXT FROM TableACursor INTO @ID END; CLOSE TableACursor DEALLOCATE TableACursor -- Repeat for TableB DECLARE TableBCursor CURSOR FOR SELECT ID FROM TableB OPEN TableBCursor FETCH NEXT FROM TableBCursor INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO TableC (Name, TableType) SELECT Name, 'B' FROM TableB WHERE ID = @ID INSERT INTO TableCChild(Name, Parent) SELECT Name, SCOPE_IDENTITY() FROM TableBChild WHERE Parent = @ID FETCH NEXT FROM TableBCursor INTO @ID END; CLOSE TableBCursor DEALLOCATE TableBCursor 

Now, my question (s):

• I've always been told that cursors are bad. But I could not find another way to do this. I am wondering if there is a way to do this using CTE?
• If the cursor is suitable in this situation, how did I do it? Is there a better way to do what I did? This does not look very dry for me, but I am not an SQL expert.

Finally, if you want to rerun the query above, here is a small script to delete the created tables.

 DROP TABLE TableAChild DROP TABLE TableBChild DROP TABLE TableCChild DROP TABLE TableA DROP TABLE TableB DROP TABLE TableC 

The correct result should look like this:

Answer 1

Here is one way to do this without a cursor or other type of type RBAR.

 ALTER TABLE TableC ADD LegacyID INT GO INSERT INTO TableC (TableType, Name, LegacyID) SELECT 'A', Name, ID FROM TableA INSERT TableCChild SELECT C.ID, AC.Name FROM TableAChild AC JOIN TableA A ON A.Id = AC.ID JOIN TableC C ON C.LegacyID = A.ID AND C.TableType = 'A' INSERT INTO TableC (TableType, Name, LegacyID) SELECT 'B', Name, ID FROM TableB INSERT TableCChild SELECT C.ID, AC.Name FROM TableBChild AC JOIN TableB A ON A.Id = AC.ID JOIN TableC C ON C.LegacyID = A.ID AND C.TableType = 'B' ALTER TABLE TableC DROP COLUMN LegacyID GO 

Answer 2

You can use merge , as described by Adam Machanic in Dr, OUTPUT or: how I learned to stop worrying and love MERGE and in this matter get a comparison between the new identifier value and the old primary key value in the table variable and its use when pasting into your child tables .

 declare @T table(ID int, IDC int); merge dbo.TableC as C using dbo.TableA as A on 0 = 1 when not matched by target then insert (TableType, Name) values('A', A.Name) output A.ID, inserted.ID into @T(ID, IDC); insert into dbo.TableCChild(Parent, Name) select T.IDC, AC.Name from dbo.TableAChild as AC inner join @T as T on AC.Parent = T.ID; delete from @T; merge dbo.TableC as C using dbo.TableB as B on 0 = 1 when not matched by target then insert (TableType, Name) values('B', B.Name) output B.ID, inserted.ID into @T(ID, IDC); insert into dbo.TableCChild(Parent, Name) select T.IDC, BC.Name from dbo.TableBChild as BC inner join @T as T on BC.Parent = T.ID; 

SQL Fiddle

Answer 3

You can use the map table to link old and new identifiers together based on some key.

In my example, I use the insertion order in TableC .

• Create a map table with an identity column.
• Add data to TableC based on the order of ID of TableA and get inserted identifiers on the map
• Use the same order of TableA.id to get ROWNUMBER() and match it with the map table id column and update old_id on the map to match TableA.id with TableC.id .
• Use the map to insert into the TableCChild table
• Truncate the card and rinse and repeat for other tables.

Request example

 CREATE TABLE #map(id int identity,new_id int,old_id int); INSERT INTO TableC ( TableType, Name )output inserted.id into #map(new_id) SELECT 'A',Name FROM TableA ORDER BY ID update m set m.old_id = ta.id FROM #map m inner join ( select row_number()OVER(order by id asc) rn,id from tableA )ta on ta.rn = m.id INSERT INTO TableCChild (Name, Parent) SELECT Name,M.new_ID FROM #Map M INNER JOIN TableAChild TA ON M.old_id = TA.Parent TRUNCATE TABLE #map INSERT INTO TableC ( TableType, Name )output inserted.id into #map(new_id) SELECT 'B',Name FROM TableB ORDER BY ID update m set m.old_id = tb.id FROM #map m inner join ( select row_number()OVER(order by id asc) rn,id from tableB )tb on tb.rn = m.id INSERT INTO TableCChild (Name, Parent) SELECT Name,M.new_ID FROM #Map M INNER JOIN TableBChild TB ON M.old_id = TB.Parent DROP TABLE #Map 

Answer 4

I just wrote the following SQL to do this if the name is unique in TableA and unique in TableB

 INSERT INTO TableCChild ( Parent, NAME ) SELECT tc.ID, ta.Name FROM TableAChild AS ta JOIN TableA a ON a.ID = ta.Parent JOIN TableC AS tc ON tc.Name = a.Name AND tc.TableType = 'A' UNION SELECT tc.ID, tb.Name FROM TableBChild AS tb JOIN TableB b ON b.ID = tb.Parent JOIN TableC AS tc ON tc.Name = b.Name AND tc.TableType = 'B' 

If the name is not unique, and only the identifier is a unique identifier, I would add LegacyId as suggested, and then the code would look like this

 /* Change Table C to Have LegacyId as well and this is used to find the New Key for Inserts CREATE TABLE TableC ( ID INT NOT NULL IDENTITY PRIMARY KEY, TableType VARCHAR(1), LegacyId INT, NAME VARCHAR(30) ); */ INSERT INTO TableC (Name, TableType, LegacyId) SELECT DISTINCT NAME, 'A', Id FROM TableA UNION SELECT DISTINCT NAME, 'B', Id FROM TableB INSERT INTO TableCChild ( Parent, NAME ) SELECT tc.ID, ta.Name FROM TableAChild AS ta JOIN TableA a ON a.ID = ta.Parent JOIN TableC AS tc ON tc.LegacyId = a.Id AND tc.TableType = 'A' UNION SELECT tc.ID, tb.Name FROM TableBChild AS tb JOIN TableB b ON b.ID = tb.Parent JOIN TableC AS tc ON tc.LegacyId = b.Id AND tc.TableType = 'B' 

Answer 5

We can achieve this by rotating the Identity column until we finish the insertion, as in the following example.

 --Create the tables CREATE TABLE TableA ( ID int not null identity primary key, Name VARCHAR(30) ); CREATE TABLE TableAChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_A FOREIGN KEY (Parent) REFERENCES TableA(ID) ); CREATE TABLE TableB ( ID int not null identity primary key, Name VARCHAR(30) ); CREATE TABLE TableBChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_B FOREIGN KEY (Parent) REFERENCES TableB(ID) ); CREATE TABLE TableC ( ID int not null identity primary key, TableType VARCHAR(1), Name VARCHAR(30) ); CREATE TABLE TableCChild ( ID int not null identity primary key, Parent int not null, Name VARCHAR(30), CONSTRAINT FK_C FOREIGN KEY (Parent) REFERENCES TableC(ID) ); -- Insert some test records.. INSERT INTO TableA (Name) Values ('A1') INSERT INTO TableAChild (Name, Parent) VALUES ('A1Child', SCOPE_IDENTITY()) INSERT INTO TableB (Name) Values ('B1') INSERT INTO TableBChild (Name, Parent) VALUES ('B1Child', SCOPE_IDENTITY()) SET IDENTITY_INSERT TableC ON INSERT INTO TableC(ID, TableType, Name) SELECT ID, 'A', Name FROM TableA INSERT INTO TableCChild(Parent, Name) SELECT Parent, Name FROM TableAChild DECLARE @MAXID INT SELECT @MAXID = MAX(ID) FROM TableC PRINT @MAXID SET IDENTITY_INSERT TableC ON INSERT INTO TableC(ID, TableType, Name) SELECT ID + @MAXID, 'B', Name FROM TableB SET IDENTITY_INSERT TableC OFF INSERT INTO TableCChild(Parent, Name) SELECT Parent + @MAXID, Name FROM TableBChild SET IDENTITY_INSERT TableC OFF SELECT * FROM TableC SELECT * FROM TableCChild DROP TABLE TableAChild DROP TABLE TableBChild DROP TABLE TableCChild DROP TABLE TableA DROP TABLE TableB DROP TABLE TableC 

Answer 6

If you need to insert records into the third table, TableC and TableCChild, for later use, it’s great to insert data into these tables, but if you only need these table data to use in your stored procedure, then you can also simply work with the first two tables to get the desired result.

 select * from ( select a.ID,'A' as TableType,a.Name from TableA a inner join TableAChild b on a.ID=b.ID union select a.ID,'B' as TableType,a.Name from TableB a inner join TableBChild b on a.ID=b.ID) TableC 

Similarly get TableCChild

 select * from ( select b.ID,b.Parent,b.Name from TableA a inner join TableAChild b on a.ID=b.ID union select b.ID,b.Parent,b.Name from TableB a inner join TableBChild b on a.ID=b.ID) TableCChild 

And if you need to insert into TableC and TableCChild, you need to recreate TableC with primary key by ID and TableType and disable the identifier for the ID column.