Set Based Solution

Last Post 09 Oct 2009 08:56 AM by cyorka. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
cyorka
New Member
New Member

--
09 Oct 2009 08:56 AM
Does anyone know of a set based T-SQL solution to insert data into a parent table and related data in another table at the same time? The problem is not knowing the primary key values to link the data in related table. Below is a simplified version of what I'm trying to do. The temp table represents data gathered from a query from other tables in the database. The code values are not unique.

I have searched high and low for a solution but have not found anything. Any help is appreciated.

CREATE TABLE Parent
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)
)

CREATE TABLE Related
(
ID INT IDENTITY(1,1),
ParentID INT,
Code2 CHAR(2),
CONSTRAINT PK_Related_ID PRIMARY KEY (ID),
CONSTRAINT FK_Related_ParentID
FOREIGN KEY(ParentID)
REFERENCES Parent (ID)
)

CREATE TABLE #TmpOther
(
Code1 CHAR(1),
Code2 CHAR(2)
)

INSERT #TmpOther VALUES('A','A1')
INSERT #TmpOther VALUES('D','D2')
INSERT #TmpOther VALUES('J','J2')
INSERT #TmpOther VALUES('F','F3')

INSERT Parent
SELECT Code1
FROM #TmpOther

SELECT * FROM Parent

-- Set based INSERT for table Related??

DROP TABLE #TmpOther
DROP TABLE Related
DROP TABLE Parent

insert into related(ParentId,Code2)
Select Parent.Id,
Code2
from Parent,
#TmpOther
where Parent.Code1 = #TmpOther.Code1

/*
data for table Parent
ID Code1
1 A
2 D
3 J
4 F

Desired data for table Related
ID ParentID Code2
1 1 A1
2 2 D2
3 3 J2
4 4 F3
*/


Acceptable Use Policy
---