How to insert values to identity column explicitly
How many times happened to you that break the relationships apart to truncate the tables just for reset the identity columns? Nevertheless it has happened to me so many times.
But yesterday I got to know there are smarter ways to do that.
One is setting IDENTITY_INSERT property to ON. It’s so easy and effective.
Syntax is
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON OFF }
Example
SET IDENTITY_INSERT employees ON
Arguments are
Database - Is the name of the database in which the specified table resides.
owner - Is the name of the table owner.
table - Is the name of a table with an identity column.
Keep on mind that at any time, only one table in a session can have IDENTITY_INSERT property set to ON. Otherwise if you try to set for another table SQL server returns an error.
posted by Madhawa @ 1:28 AM
3 Comments:
Hey, this allows to insert values explicity to identity column not to re-set. Hope that's what you said.
yeah... Dinesh but we can delete all the records in the table and then insert the first record setting identity column to 1 no?
yes, but if your ultimate target is, resetting, better go something like DBCC CHECKIDENT.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home