how to ON/OFF identity field through sql script?
-
i m using sqlserver 2000 i have a table that has the primary key set as identity i want to make that off insert one row and value that column that is set as identity and then again make that on and i want to do that through sql script. i try this code CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50)) INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation') ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2) And have got this error always: Incorrect syntax near the keyword 'IDENTITY'. Why I got this?
-
i m using sqlserver 2000 i have a table that has the primary key set as identity i want to make that off insert one row and value that column that is set as identity and then again make that on and i want to do that through sql script. i try this code CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50)) INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation') ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2) And have got this error always: Incorrect syntax near the keyword 'IDENTITY'. Why I got this?
Not quite clear on what it is you are trying to do, but it isn't allowed. I've included the ALTER COLUMN description from BOL below. If you need to perform this kind of change you'll need to create a table which matches your customers table (MyCustomers_copy) AND has the new definition for the identity column. Then you'll need to copy all the data from the customers table to the copy table. Then drop the original customers table and rename (sp_rename stored procedure) the copy table to the name of the original customers table. If all you're trying to do is insert an explicit identity value instead of letting SQL Server do it for you, then you just need to use IDENTITY_INSERT
SET IDENTITY_INSERT MyCustomers ON INSERT INTO MyCustomers(CustID, CompanyName) VALUES(202, 'A. Datum Corporation') SET IDENTITY_INSERT MyCustomers OFFThat will allow you to insert any value you like for CustID. However, if the value already exists you'll get a primary key violation error. Usually this is not used from application code, only for coping data from one database to another. ALTER TABLE Documentation from BOL: ALTER COLUMN Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL). The altered column cannot be any one of the following: A column with a timestamp data type. The ROWGUIDCOL for the table. A computed column or used in a computed column. Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size. Used in statistics generated by the CREATE STATISTICS statement. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN. Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint. Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed. Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed. The data type of text, ntext and image columns can be changed only in the following ways: text to varchar(max), nvarchar(max), or xml ntext to varchar(max), nvarchar(max), or xml image to varbinary(max) Some da