Menu
For a sql script I'm working on, I need to programmatically remove the identity, identity seed, and identity increment for a column in an existing table, then add them back to the table at the end of the script. Does anyone have a reference or an example on how to do this?
628
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
You should do this:
SET IDENTITY_INSERT <TableName> ON
-- Do the inserting in the table with name <TableName>
SET IDENTITY_INSERT <TableName> OFF
For more details look in the MSDN.
145
Yes, you just do this:
SET IDENTITY_INSERT [TABLE] ON
And then back on:
SET IDENTITY_INSERT [TABLE] OFF
This will allow you to enter manual data in the identity column.
http://msdn.microsoft.com/en-us/library/ms188059.aspx
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
