Capture identity values while inserting multiple rows in Sql Server 2005

• bijayanix24

  SSC-Addicted

  Points: 447

  More actions

  February 16, 2010 at 7:16 am

  #216657

  Let's start with 2 tables CustomerTable and CustomerToInsertTable. The scenario is- we are getting a complete list of all the customers from a vendor from CustomerToInsertTable and we want to update CustomerTable from CustomerToInsertTable the by inserting the rows that don't already exist. However we also need to insert those new rows in other multiple tables.

  STEP 1 : The script below will create new tables.

  CREATE TABLE [CustomerTable]

  (

  RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

  CustomerKey INT,

  CustomerName NVARCHAR(50) NOT NULL,

  UserName NVARCHAR(25) NOT NULL,

  Password NVARCHAR(25) NOT NULL

  )

  CREATE TABLE CustomerToInsertTable

  (

  CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

  CustomerName NVARCHAR(50) NOT NULL,

  UserName NVARCHAR(25) NOT NULL,

  Password NVARCHAR(25) NOT NULL,

  InsertedIdentityValue INT NULL

  )

  STEP 2 : Now let's insert 16 new rows to the CustomerToInsertTable.

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES ('Windell L.', '1108', '14117')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Rad', '1114', '8927')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Charles F.', '1127', '12287')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Andrew R.', '1128', '9605')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Steven R.', '1191', '8420')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Robert', '1200', '18971')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Ronald F.', '1205', '13730')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Deidra', '1209', '9902')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Rena', '1264', '9611')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Ann L', '1290', '9548')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Robert', '1302', '9083')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Jerry H.', '1313', '14735')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Joan', '1322', '10211')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Mark', '1355', '10028')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Terry', '1358', '8564')

  INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Bret', '1373', '10067')

  STEP 3 : Copy top 5 rows to CustomerTable from CustomerToInsertTable.

  INSERT [CustomerTable](CustomerKey, CustomerName, UserName, Password)

  SELECT TOP 5 CustomerID, CustomerName, UserName, Password

  FROM CustomerToInsertTable ORDER BY CustomerID

  STEP 4 :

  So we are having 16 rows in CustomerToInsertTable and only 5 in CustomerTable. We need to insert rows in the CustomerTable by getting new rows from the CustomerToInsertTable and also save them in a temporary table, that can be used to insert or update any other tables.

  Here I have updated the CustomerToInsertTable.InsertedIdentityValue with the CustomerTable.RowID.

  --Declare Temp Table @InsertedRows

  DECLARE @InsertedRows TABLE (RowID INT, CustomerKey INT, CustomerName NVARCHAR(50) )

  --Insert Non existing rows in CustomerTable From CustomerToInsertTable

  INSERT CustomerTable (CustomerKey, CustomerName, UserName, Password)

  OUTPUT inserted.RowID,

  inserted.CustomerKey,

  inserted.CustomerName

  INTO @InsertedRows

  SELECT CustomerID, CustomerName, UserName,Password

  FROM CustomerToInsertTable AS CIT

  WHERE NOT EXISTS (SELECT 1 FROM CustomerTable WHERE CustomerKey = CIT.CustomerID)

  --Get the new rows inserted

  SELECT * FROM @InsertedRows

  --Update CustomerToInsertTable

  UPDATE CustomerToInsertTable

  SET InsertedIdentityValue = IR.RowID

  FROM CustomerToInsertTable CIT

  JOIN @InsertedRows IR ON IR.CustomerKey = CIT.CustomerID

  Eliza

  Cheers,
  Bijayani
  Proud to be a part of Team Mindfire.

  Mindfire[/url]: India's Only Company to be both Apple Premier & Microsoft Gold certified.

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  February 16, 2010 at 8:38 am

  #1119318

  What are you trying to achieve ? What problems are you facing ?

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  February 16, 2010 at 9:42 am

  #1119345

  you need sql 2008 ( which is this forum ) and use the output statement ( see merge for further info )

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/