Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mapping Old identity values to new ones Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 2:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:49 AM
Points: 32, Visits: 135
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?
Post #1434777
Posted Monday, March 25, 2013 3:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,652, Visits: 4,731
Is there a UNIQUE column(s) in your table tblOrderItems other than the IDENTITY field?


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1434800
Posted Monday, March 25, 2013 4:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,652, Visits: 4,731
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
WHERE A.OrderID = @OrderID

INSERT #temp_result( OldOrderItemID )
SELECT A.OrderItemID
FROM [dbo].[tblOrderItems] A
WHERE A.OrderID = @OrderID

UPDATE R
SET R.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
WHERE R.NewOrderItemID IS NULL




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1434806
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse