Need to Insert To Parent-Child Table With IDs and Relationship In Other Tables

Question

Need to Insert To Parent-Child Table With IDs and Relationship In Other Tables

larsosman56

SSC Enthusiast

Points: 111
More actions
December 16, 2008 at 9:11 am

#197597

Hi there,
I can use some SQL advice with what I suspect is easy problem.
I need to insert rows of two items as a pair into a table (e.g. parent, child) where the id for these items is stored in another table where the PK for these items is stored. Basically, I need to update a parent_child table with the IDs of each combination when I only have the labels for the items:
Items
-----
id item
1 item1
2 item2
3 item3
Relationship
-----------
parent child
item1 item2
item1 item2
Target
------
parent_id child_id
1 2
1 3
Does this make sense?
My Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 1

Sure, you can use the OUTPUT clause of the INSERT query. This will allow you to capture ID's created during the INSERT and you can then pass them on to the next INSERT. Syntax is right there in Books Online.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

larsosman56 (12/16/2008)
Hi there,
I can use some SQL advice with what I suspect is easy problem.
I need to insert rows of two items as a pair into a table (e.g. parent, child) where the id for these items is stored in another table where the PK for these items is stored. Basically, I need to update a parent_child table with the IDs of each combination when I only have the labels for the items:
Items
-----
id item
1 item1
2 item2
3 item3
Relationship
-----------
parent child
item1 item2
item1 item2
Target
------
parent_id child_id
1 2
1 3
Does this make sense?
My Thanks!

This sounds like a bad design, or you must have very limited access. But anyway, I guess this should come close what you need

INSERT INTO #Target (ParentID, ParentItem, ChildID, ChildItem)

SELECT I_Parent.ID, I_Parent.Item ParentItem, I_Child.ID, I_Child.Item ChildItem

FROM #Relation T

INNER JOIN #Items I_Parent ON I_Parent.ID = T.ParentID

INNER JOIN #Items I_Child ON I_Child.ID = T.ParentID

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 3

Thanks for the help. To be clear, you're suggesting two inserts. One to load the parent ids and a second to load the child IDs? I think that's where you're headed. And thanks for the Books Online tip. Can you put a little more bait on the hook -- what topic should I hit? I have to admit, I am picking up speed quickly, but I am still in the right lane (on American roads). Thanks again.

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 4

Hi there.

Curious why the design may be bad. I have the items in one table along with their attributes, and the way they interact with one another (hierarchies) in another. The hierarchies are provided by the user via a flat-file load, usually after the items have already been defined. The hierarchies are dependent on some other data, such as time and other attributes. That's why I have segregated them into 2 tables.

Thanks!

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 5

You'll probably need three inserts, but you can insert into each of the parent tables, get the ID's generated and then insert into the third table based on those ID's using the OUTPUT clause. This is the link to the local BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 6

larsosman56

SSC Enthusiast

Points: 111

December 16, 2008 at 10:46 am

#913330

Bingo! Got it. Thanks

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 7

larsosman56 (12/16/2008)
Hi there.
Curious why the design may be bad. I have the items in one table along with their attributes, and the way they interact with one another (hierarchies) in another. The hierarchies are provided by the user via a flat-file load, usually after the items have already been defined. The hierarchies are dependent on some other data, such as time and other attributes. That's why I have segregated them into 2 tables.
Thanks!

The bad about the design is that I would like to see the id's together in the relationship table, it looked like you have only the item names there.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2