SQL Server 2000之identity

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO

如果

INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO

就会报错

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'products' when IDENTITY_INSERT is set to OFF.

如何解决:

SET IDENTITY_INSERT products ON

完整code:

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GO

SELECT * 
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

更多可参考:http://blog.youkuaiyun.com/seabluecn/archive/2007/10/19/1833173.aspx