SQL Server 2000之identity

来源：互联网发布：域名还要备案吗编辑：程序博客网时间：2024/05/16 17:06

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'GOSELECT * FROM productsGO-- 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 ONGO-- Attempt to insert an explicit ID value of 3INSERT INTO products (id, product) VALUES(3, 'garden shovel').GOSELECT * FROM productsGO-- Drop products table.DROP TABLE productsGO

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