Insert multiple Order Header IDs into OrderDetail? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert multiple Order Header IDs into OrderDetail?

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2012-05-22 : 12:39:04

Hello.

I need to create an INSERT statement that pulls all of our daily orders from our Ordering system and places them into another Order_Header & Order_Detail table. I can insert into the Order_Header table successfully, however I need to find a way to "remember" the Order_Header_IDs of all Order_Header records when inserting each record into the Order_Details table.

So for example, lets say we have the following 3 orders in our Ordering System:
ORDERSYSTEM_ORDER_ID | ORDERSYSTEM_DATE | ORDERSYSTEM_LASTNAME
----------------------------------------------------------
111 | 4/12/2012 | Smith
222 | 4/28/2012 | Jones
333 | 5/8/2012 | Andrews

I then successfully insert these records into a separate order_header table using a single query, which results in records that look like this:

ORDER_HEADER_ID | ORDERSYSTEM_ORDER_ID | ORDER_DATE | ORDER_LASTNAME
--------------------------------------------------------------------
1 | 111 | 4/12/2012 | Smith
2 | 222 | 4/28/2012 | Jones
3 | 333 | 5/8/2012 | Andrews

My question is, once I've inserted these orders into Order_Header, how do I then remember these ORDER_HEADER_ID values when inserting into ORDER_DETAILS?

For example, my order details should look like this:

ORDER_DETAIL_ID | ORDER_HEADER_ID | ORDER_ITEM | ORDER_QTY
------------------------------------------------------------
1 | 1 | BL3456-1 | 5
2 | 1 | RN1826-18 | 1
3 | 1 | VR5959-6 | 2
4 | 2 | YT4494-19 | 1
5 | 3 | YB5569-1 | 16
6 | 3 | QQ5400-2 | 4

How can I insert this order header id?

Thanks for the help (hopefully I explained my issue clearly

)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-22 : 14:25:18

can you please provide sample ORDER_DETAILS data for Smith Jones and Andrews.
you can "remember" by using the INSERTED. feature of sql to a table variable and dump the inserted data into that table variable


declare @Order_Header table(ORDERSYSTEM_ORDER_ID int, ORDERSYSTEM_DATE datetime, ORDERSYSTEM_LASTNAME varchar(255))


create table dbo.Order_Header(ORDERSYSTEM_ORDER_ID int, ORDERSYSTEM_DATE datetime, ORDERSYSTEM_LASTNAME varchar(255))
insert into dbo.Order_Header
select 111, '4/12/2012', 'Smith'
UNION
Select 222,  '4/28/2012' , 'Jones'
UNION
SELECT 333 , '5/8/2012' , 'Andrews'

create table dbo.Order_Header_part_deux(ORDERSYSTEM_ORDER_ID int, ORDERSYSTEM_DATE datetime, ORDERSYSTEM_LASTNAME varchar(255) )
insert into dbo.Order_Header_part_deux
OUTPUT INSERTED.ORDERSYSTEM_ORDER_ID, INSERTED.ORDERSYSTEM_DATE, INSERTED.ORDERSYSTEM_LASTNAME
        INTO @Order_Header

select 111, '4/12/2012', 'Smith'
UNION
Select 222,  '4/28/2012' , 'Jones'
UNION
SELECT 333 , '5/8/2012' , 'Andrews'

select * from @Order_Header

drop table Order_Header_part_deux
drop table Order_Header

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources