Bulk Copy program (BCP) in sql server

With the help of Bulk copy program utility we can copies the data from an instance of Microsoft SQL Server to file in a user-specified format or file in a user-specified format to SQL server vice versa.

We can also export the data in flat files according to select query using queryout,

Let’s learn step by step how we use BCP.

There are two way to use BCP

Ø  Using command line

Ø  Using sql server

Syntax of BCP

BCP [Datebasename].[dbo].[TableName] OUT|IN|queryout [Destination where you want to store the flat file] [Arguments] Example:- BCP test.dbo.Emp OUT D:\Emp.txt -T -c

OUT- when we are exporting data from sql server to flat file

IN- when we are Importing the data from the file to sql server

Queryout: using in T-Sql query

Arguments	Description
[-S server name]	server name
[-T trusted connection]	trusted connection
[-d database name]	database name
[-P password]	Password
[-c character type]	Character type
[-o out file]	Out File
[-U username]	User Name
[-x generate xml format file]	XML file

(A). Export data with trusted connection

BCP test.dbo.Emp OUT D:\Emp.txt -T -c

Open the command prompt

Before running the command see in D drive nothing is there.

  

Now I am executing BCP command in command prompt

  

Press enter

    

See here 12 rows are copies. Now we can see the files

   

It is created on the desired location.

We can achieve same thing using sql query.

 XP_cmdshell – It returning the sql row set.

Syntax is below.

exec Test..xp_cmdshell 'BCP test.dbo.Emp OUT D:\EmpDB.txt -T -c'

    

Now see in the D drive

   

(B) Export data in mix mode authentication with password

BCP test.dbo.Emp OUT D:\EmpMixMode.txt -c -Usa -SBagesh-PC –P******

 

   

(C) Import Data from file to sql server with trusted connection

We must have the same definition of the table in which we want to export it.

--Definition  of Emp table CREATE TABLE [dbo].[Emp](       [EmpID] [nchar](10) NOT NULL,       [EmpName] [nchar](50) NULL,       [EmpAdd] [nchar](100) NULL,       [Salary] [int] NULL) --Definition  of Emp table CREATE TABLE [dbo].[Emp1](       [EmpID] [nchar](10) NOT NULL,       [EmpName] [nchar](50) NULL,       [EmpAdd] [nchar](100) NULL,       [Salary] [int] NULL )

Now we can Import data from file to sql server.

 

See the value in table

   

Using T-sql

exec test..xp_cmdshell 'BCP test.dbo.Emp1 In D:\Emp.txt -T -c'

    

    

(D) Export data from sql server to file based on select statement

Using cmd prompt

BCP "select EmpName,EmpAdd from test.dbo.Emp" queryout D:\Empselect.txt -T -c

 

Output file 

    

Using T-sql

exec test..xp_cmdshell 'BCP "select EmpName,EmpAdd from test.dbo.Emp" queryout D:\Empselect.txt -T -c'