1

i have 2 tables. One is Order header the other one is order lines. Order header:

OrderID Customer IntegrationID(Identity auto Incremental)

Order lines :

OrderID Itemid IntegrationID

The thing i want to do: i run a stored procedure which populates these 2 tables with data and i want the order line to have the same IntegrationID as the order header. For example when i insert to order header:

OrderID Customer IntegrationID(Identity auto Incremental)
500       5          1
501       4          2

And in the order lines i want to have the data like this:

OrderID Itemid IntegrationID
 500     101         1
 500     102         1
 501     102         2

Any ideas how can i achieve that? Orderheader and orderlines are linked via ORDERID field.

user2893780
  • 121
  • 1
  • 2
  • 9
  • what is initial data in Order Line table? – Sandip - Frontend Developer Apr 20 '17 at 05:31
  • Its orderid and Itemid – user2893780 Apr 20 '17 at 05:34
  • What you have tried, show your query – Sandip - Frontend Developer Apr 20 '17 at 05:35
  • Why not just insert into order line immediately after order header in the stored proc? Can you include stored proc code. – Sasang Apr 20 '17 at 05:35
  • stored procedure is simple it just inserts lines into salestable and saleslines: iNSERT into SalesHeader OrderID,Customer select orderid,customer from SalesTable insert into OrderLines OrderId itemid select orderid,itemid from SalesLine. T – user2893780 Apr 20 '17 at 05:49
  • The reason i want to to this, is that these records are processed by other system. And there may be cases that if an order is modified for a couple of times in the system here it would appear multiple times. So that's why i want to separate them at each new insert. – user2893780 Apr 20 '17 at 06:00
  • If you want to insert **multiple orders** with the corresponding order lines, you can use [the technique I've posted here.](http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231) If you want to insert a single order, then it's much simpler. – Zohar Peled Apr 20 '17 at 06:03
  • From where do you insert these records? Can you use table valued parameters? – Zohar Peled Apr 20 '17 at 06:07
  • Im recieving records from a view – user2893780 Apr 20 '17 at 06:28

1 Answers1

0

Its Simple,
For Example In Your Stored Procedure you insert record in table "Order Header"

Insert into (col1,col2) values ("val1","val2")

then you should use the following query to insert into table "Order Line"

SELECT @new_identity = SCOPE_IDENTITY()

Then insert this @new_identity in the query

Insert into Order Line (OrderID Itemid IntegrationID) values 
("value1","value2",@new_identity)

but make sure these both query executes in same stored procdure if you have individual SP for both then you should have return from the first SP

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Ahmed
  • 26
  • 2
  • Thank you for reply but ,correct me if I'm wrong, but this only works then one order is inserted. Am I correct? – user2893780 Apr 20 '17 at 06:21