Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Explicitly inserting data into an IDENTITY column / How to Insert Values into an Identity Column in SQL Server

Identity Columns are commonly used as primary keys in database tables.  These columns automatically assign a value for each new row inserted. We can explicitly insert data into an identity column using below methods.

MULTIPLE COLUMNS – One of the columns is Identity

--
/*
Explicitly inserting data into an IDENTITY column
OR
How to Insert Values into an Identity Column in SQL Server
*/

--In case of Multiple Columns

CREATE TABLE testIdentityMultipleColumn
(
	 ID INT IDENTITY(1,1)
	,Name VARCHAR(1)
)
GO

SET IDENTITY_INSERT testIdentityMultipleColumn ON

INSERT INTO testIdentityMultipleColumn
(ID,NAME) VALUES ( 1 , 'P' )

SET IDENTITY_INSERT testIdentityMultipleColumn OFF

--


SINGLE COLUMN – IDENTITY

In case of Single Column

--
/*
Explicitly inserting data into an IDENTITY column
OR
How to Insert Values into an Identity Column in SQL Server
*/

--In case of Single Column

CREATE TABLE testIdentity
(
	ID INT IDENTITY(1,1)
)
GO

INSERT INTO testIdentity DEFAULT VALUES
--

Note – Please note that we CANNOT update the value present in the Identity Column. Keep learning. We all need to learn.

http://MSBISkills.com
http://Pawankkmr.Wordpress.com

Advertisements