DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to reseed the identity value on a table?

Execute the following Microsoft SQL Server T-SQL scripts to demonstrate how to change IDENTITY sequence:

-- CHECKIDENT method for reseeding identity column - QUICK SYNTAX

DBCC CHECKIDENT('dbo.Customer', RESEED, 100 )

------------

 

-- Check current seed value

DBCC CHECKIDENT('Production.Product', NORESEED);

-- Checking identity information: current identity value '999', ...

------------

 

-- SQL INSERT basic syntax - INSERT VALUES - INSERT SELECT

USE AdventureWorks2008;

CREATE TABLE ProductAlpha (

  ProductID INT IDENTITY(1,1) PRIMARY KEY,

  ProductName varchar(64) UNIQUE,

  Price money not null CHECK   (Price > 0),

  ModifiedDate date default (getdate()));

 

INSERT ProductAlpha (ProductName, Price) VALUES

('BMW 330i automatic', $50000),

('BMW 330i manual', $49000),

('BMW 528i automatic', $60000)

 

INSERT ProductAlpha (ProductName, Price)

SELECT 'BMW 128i manual',$30000

 

SELECT * FROM ProductAlpha ORDER BY ProductID

GO

/*

ProductID   ProductName             Price       ModifiedDate

1           BMW 330i automatic      50000.00    2010-05-01

2           BMW 330i manual         49000.00    2010-05-01

3           BMW 528i automatic      60000.00    2010-05-01

4           BMW 128i manual         30000.00    2010-05-01

*/

DROP TABLE ProductAlpha

------------

 

-- SELECT INTO table create for testing

USE tempdb

SELECT ProductID, ProductName=Name, ListPrice

INTO Product

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0

--(304 row(s) affected)

SELECT MAX (ProductID) FROM Product

GO

-- 999

-- CHECKIDENT METHOD

DBCC CHECKIDENT('dbo.Product', RESEED, 2000)

GO

/*

Checking identity information: current identity value '999',

current column value '2000'

*/

INSERT Product(ProductName, ListPrice)

VALUES ('SQLUSA Model Battery Assist Touring Bike Red 36', 2399.00)

GO

SELECT SCOPE_IDENTITY()

GO

-- 2001

SELECT MAX (ProductID) FROM Product

GO

-- 2001

 

-- IDENTITY INSERT METHOD

SET IDENTITY_INSERT dbo.Product ON

GO

INSERT Product(ProductID, ProductName, ListPrice)

VALUES (1500, 'SQLUSA Model Electric Mountain Bike Gray 42', 3399.00)

GO

SELECT SCOPE_IDENTITY()

GO

-- 1500

SET IDENTITY_INSERT dbo.Product OFF

GO

DROP TABLE tempdb.dbo.Product

 

Related articles:

 

DBCC CHECKIDENT (Transact-SQL)

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE