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