Four Ways to Insert Value into IDENTITY Column in SQL Server


So we can summarize four ways to add values to identity column in SQL Server.

1.SET IDENTITY_INSERT ON;

SET IDENTITY_INSERT [dbo].test1 ON
Go
Insert into test1 (col1) values(11),(12)

SET IDENTITY_INSERT [dbo].test1 OFF
Go

2. Insert into table with default values ;

Insert into test1 default values ;

3. Bcp with -E flag along wiht a format file;

4. Bulk insert with KEPPIDENTITY.



use mytest4
go
 
create table test1 (col1 int primary key identity(1,1))
Go
Insert into test1 default values
go 5

 
SET IDENTITY_INSERT [mytest4].[dbo].test1  ON
Go 
Insert into test1 (col1) values(11),(12)
 
SET IDENTITY_INSERT [mytest4].[dbo].test1  OFF
Go
 
 
--
select * from test1
--drop table test1
  

--Create a format file for bcp and bulk insert
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[test1] format nul -c -x -f  C:\temp\FORMATFIle.xml -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql
 
  --  -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited 
 
 
----bcp Export
--DECLARE @sql2 VARCHAR(4000) 
--SET @sql2='bcp [mytest4].[dbo].[test1] out "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\t  -b 1000 -S'+ @@servername
--exec master..xp_cmdshell @sql2
 
--go
 
 --Remove data from table
 Truncate table test1
 
--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[test1] IN "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -T  -t\t  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql3

--/* "-E Specifies that identity value or values in the imported data file are to be used for
--the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored.
--*/
 


select * from test1

 

 
/*
BULK Insert Options
*/
 
  --Remove data from table
 Truncate table test1
----Option 2: Bulk insert with identity insert
 
BULK INSERT [mytest4].[dbo].test1
   FROM 'C:\temp\test1.csv'
   WITH (
      KEEPIDENTITY
     -- ,FORMATFILE='C:\temp\FORMATFIle.xml'
   );
 /*
 KEEPIDENTITY:
 Specifies that identity value or values in the imported data file are to be used for the identity column. 
 */
 
 
select * from test1
 
drop table test1


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s