Bulk copy in Sql server & C#

Bulk copy in Sql server & C#

                  Bulycopy is an utility to copy data from source from destination for larger data . It can be text to Microsoft Sql server or Sql server to text file or text file to text file or sql server to sql server . This is and utility present in both Sql server and C#/Vb.net . Developer decides which way , he should use . Both bulk copy operation comes under transaction management . Bulk copy can be Single operation or Multiple operation .The simplest approach to performing a SQL Server bulk copy operation is to perform a single operation against a database , that is called Single operation , You can perform multiple bulk copy operations using a single instance of a SqlBulkCopy class , that is called Multiple operation . In this article we will see some simple example of bulkcopy . First we will see how to do with sql server.

Sql Server to text file :

--1. we are createing a table first

CREATE table tbl_bulkcopy

(

id    INT identity,

name  VARCHAR(50),

roll  INT

)

--insert data to the table

INSERT INTO tbl_bulkcopy(name,roll) VALUES('Ayan',20)

INSERT INTO tbl_bulkcopy(name,roll) VALUES('John',21)

--declare some use ful variable

DECLARE @fp             NVARCHAR(200)

DECLARE @fn             NVARCHAR(200)

DECLARE @sql            NVARCHAR(200)

DECLARE @opqry            NVARCHAR(200)

--set values of the variable

SELECT @fp ='C\copypath\'

SELECT @fn ='bulkcopy.txt'

SELECT @opqry='type '+@fp+@fn

---

---write the select query

SELECT @sql='SELECT name ,roll FROM tbl_bulkcopy'

---create the bulk copy script

SET @sql =  'bcp ' + '"' +  @sql + '"' +  '  queryout "' + @fp + @fn +'"   -c -q -C1252 -T '

--run the script in command shell

EXECUTE master..xp_cmdshell @sql

--

--to see the output of the file , please write the code

--or you can open the file directly

EXECUTE master..xp_cmdshell @opqry

Text file (.txt, csv) to Sql Server

--1. we are createing a table first

CREATE table tbl_bulkcopy

(

 id    INT identity,

[field1] VARCHAR(500),

[field2] VARCHAR(500),

[field3] VARCHAR(500),

)

--place the correct path of your file     

--you can specify field deliminator , row deliminator

--you can spcify error file also

BULK INSERT tbl_bulkcopy

FROM 'C:\dataloading\ex311316\outgoing\10_Group_Sub_Group.txt'

WITH

(

    FIRSTROW = 2,

    FIELDTERMINATOR = ',',  --CSV field delimiter

    ROWTERMINATOR = '\n',   --Use to shift the control to next row

    ERRORFILE = 'C:\dataloading\ex311316\outgoing\bulkcopy2p.txt',

    TABLOCK

)

EXECUTE master..xp_cmdshell @opqry

--

--

SELECT field1,field2,field3 FROM tbl_bulkcop