The IDENTITY columns
are auto incrementing columns provided by SQL Server.
There can only be
one IDENTITY column per table.
SQL Server will take
care of incrementing this column automatically.
The following query
gives you the list of all the identity column names in a database along with
the table names
INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t
WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1
= 'Base Table'
NOT LIKE 'dt%'
NOT LIKE 'MS%'
NOT LIKE 'syncobj_%'
If you are inserting
data from some other source to a table with an identity column, you need to
ensure you retain the identity values, you can temporarily allow inserts to
the identity column. Without doing so, you will receive an error if you
attempt to insert a value into the identity column.
To allow inserts into
the identity column of a table, execute the following statement
After we are done with
insert on the table, we need to execute the following statement
This is because only a
single table in a session can have the identity_insert set to on. If you
attempt to enable this for a table and another table already has this
enabled, you will receive an error and will not be able to do so until you
first turn this off for the other table.
While inserting the data
into a table with identity column we also have to mention all the column