|
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)
|
