Sample Code for Bulk Insert and BCP




use mytest4
go

Create Table subtest
(
[ID] [int] IDENTITY(1,1) NOT NULL,
emailID Varchar(50)
)
Go
Insert into subtest values('aaa@abc.com'),('asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  ON
Go

Insert into [mytest4].[dbo].[subtest] (ID, emailID) values(11,'aaa@abc.com'),(12,'asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  OFF
Go

go
--select * from Subtest
--drop table subtest
 


DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[subtest] format nul -c -x -f  C:\temp\FORMATFIle.xml -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].[subtest] out "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\,  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql2

go


--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[subtest] IN "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -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.
*/


--Truncate table Subtest
--go


/*
BULK Insert Options
*/

--Option 1: Bulk insert WITHOUT  identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
          FORMATFILE='C:\temp\FORMATFIle.xml'
   );


--Option 2: Bulk insert with identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\temp\FORMATFIle.xml'
   );

--Option 3:  Bulk insert with identity insert   No format file
Bulk Insert Subtest
From 'C:\temp\subids.csv'
With 
(
KEEPIDENTITY,
FieldTerminator =','
--FORMATFILE = 'C:\temp\FORMATFIle.xml'
);


select * from Subtest

drop table subtest
 


 
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