Inserting data into tables with identity columns

Document Sample
Inserting data into tables with identity columns Powered By Docstoc
					Identity Columns

>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

SELECT
t.TABLE_NAME,c.COLUMN_NAME,c.TABLE_CATALOG,c.TABLE_SCHEMA FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1
AND t.TABLE_TYPE = 'Base Table'
AND t.TABLE_NAME NOT LIKE 'dt%'
AND t.TABLE_NAME NOT LIKE 'MS%'
AND t.TABLE_NAME 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

set identity_insert <tablename> on

After we are done with insert on the table, we need to execute the following statement

set identity_insert <tablename> off

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 names
explicitly as shown in the below query

				
DOCUMENT INFO
Shared By:
Stats:
views:8
posted:2/1/2013
language:English
pages:1
Description: This document explains how to insert data into tables with identity columns