CourtesyIndia.com
Trusted since 2005.
 home / software / tips and tricks / How to Insert Values into an Identity Column in SQL Server

How to Insert Values into an Identity Column in SQL Server

Updated:  07/15/2010 13:07 PM
Author:  Shiju Mathews

Status:    Resolved.

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But what if you want to insert your own value into the column?

You can do this by setting 'ON' or 'OFF' the IDENTITY_INSERT, SET IDENTITY_INSERT. This allows explicit values to be inserted into the identity column of a table.

Syntax: SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Arguments

  • 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.





    When IDENTITY_INSERT is 'OFF' each new row is created with automatically assigned 'Identity column' values.
    When IDENTITY_INSERT is 'ON' each new row is created with custom supplied 'Identity column' values.

    Tips and Tricks

    Interview Questions for C#

    Interview Questions for ASP.Net

    Interview Questions for SQL Server

    Tags: How to Insert Values into an Identity Column in SQL Server
    Updated on: November 2024