Updating an identity column


05-Oct-2016 19:06

Previously this required some type of loop or temporarily altering the target table.

We'll start with two tables: a product table and table of products to insert.

Product Number)INSERT Product ([Name], Product Number, List Price) OUTPUT inserted. Product Number) Product ID Name Product Number List Price ----------- ----------------------------------- --------------------- ------------- 451 LL Bottom Bracket BB-7421 53.99 452 ML Bottom Bracket BB-8107 101.24 453 HL Bottom Bracket BB-9108 121.49 . We have the identity values in the result set but we don't have a way to work with the data and we don't have a way to tie it back to the original source row. First we'll need to store this result set in a table variable.

I'll also remove a few columns to make it easier to read and add an update statement to save the identity value.

You'll notice I only put 450 of the products in the Product table to start with but all 504 in the Products To Insert table.

A simple script to insert the new products looks like this: use tempdb GO INSERT Product ([Name], Product Number, List Price) SELECT [Name], Product Number, List Price FROM Products To Insert I WHERE NOT EXISTS (SELECT 1 FROM Product WHERE Product Number = I. List Price SELECT [Name], Product Number, List Price FROM Products To Insert I WHERE NOT EXISTS (SELECT 1 FROM Product WHERE Product Number = I. 504 HL Touring Handlebars HB-T928 91.57 (54 row(s) affected) That is almost what we want.

The following script will create the tables in tempdb based on data in Adventure Works.

updating an identity column-68

adult dating sudbury massachusetts

Previously this data was only available through triggers. SELECT statement to capture all the inserted identity values.Products To Insert') AND type in (N'U')) DROP TABLE [dbo].Products To Insert GO CREATE TABLE Product ( Product ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, Product Number NVARCHAR(25) NOT NULL, List Price MONEY NOT NULL) GO CREATE UNIQUE INDEX IX_Product_Product Number ON Product ( Product Number ) GO CREATE TABLE Products To Insert ( Row ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, Product Number NVARCHAR(25) NOT NULL, List Price MONEY NOT NULL, Inserted Identity Value INT NULL) GO INSERT Product ([Name], Product Number, List Price) SELECT TOP 450 [Name], Product Number, List Price FROM Adventure Works. Product ORDER BY Sell Start Date, Product ID GO INSERT Products To Insert ([Name], Product Number, List Price) SELECT [Name], Product Number, List Price FROM Adventure Works. Product GO The Product table has an identity column as its primary key. The Products To Insert table has the Product Number column and a column for whatever identity value is inserted when we put the row into the Product table.The scenario is a vendor that sends you a complete list of all their products and you only need to insert the rows that don't already exist.

However you need to insert those new rows into multiple tables.That script looks like this: DECLARE @Inserted Rows TABLE (Product ID INT, Product Number NVARCHAR(25) ) INSERT Product ([Name], Product Number, List Price) OUTPUT inserted. Product Number INTO @Inserted Rows SELECT [Name], Product Number, List Price FROM Products To Insert AS I WHERE NOT EXISTS (SELECT 1 FROM Product WHERE Product Number = I.