March 25, 2013 at 2:46 am
Hi All,
I have a requirement in which I insert values into same table, but I need to map old identity column with those newly inserted: Below is the snippet:
1st Insert:
INSERT INTO [dbo].[tblOrders] ...........--some insert statement
SET @newOrderId = @@IDENTITY;
CREATE TABLE #temp_result (OldOrderItemID BIGINT, NewOrderItemID BIGINT)
INSERT INTO [dbo].[tblOrderItems]
([ItemTypeID]
,[OrderID]
,[Service]
)
SELECT
[ItemTypeID]
,@newOrderId
,[Service]
FROM [dbo].[tblOrderItems] A
WHERE A.OrderID = @OrderID -- Multiple records for single @orderID
In table tblOrderItems there is Identity: OrderItemID Seed 1 Increment 1
I need a table #temp_result with entry for mapping old OrderItemIDs with newly inserted OrderItemIDs without disturbing the existing tblOrderItems. Any advice?
March 25, 2013 at 3:58 am
Is there a UNIQUE column(s) in your table tblOrderItems other than the IDENTITY field?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2013 at 4:17 am
Assuming that the column combination of ItemTypeID, Service and OrderID is UNIQUE, the below code should do what you need
INSERT[dbo].[tblOrderItems]( [ItemTypeID], [OrderID], [Service] )
SELECT[ItemTypeID], @newOrderId, [Service]
FROM[dbo].[tblOrderItems] A
WHEREA.OrderID = @OrderID
INSERT#temp_result( OldOrderItemID )
SELECTA.OrderItemID
FROM[dbo].[tblOrderItems] A
WHEREA.OrderID = @OrderID
UPDATER
SETR.NewOrderItemID = NOI.OrderItemID
FROM#temp_result AS R
INNER JOIN [dbo].[tblOrderItems] AS OOI
ON R.OldOrderItemID = OI.OldOrderItemID
INNER JOIN [dbo].[tblOrderItems] AS NOI
ON OOI.ItemTypeID = NOI.ItemTypeID
AND OOI.Service = NOI.Service
AND NOI.OrderID = @newOrderId
WHERER.NewOrderItemID IS NULL
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy