Generating Files Through BCP Utility In SQL Server

In this article, We will learn BCP query and how to generate files(txt,CSV, json) through BCP utility in SQL Server.

What is BCP(bulk copy program) in SQL Server:

BCP is used to bulk copies data into the data file in a user-specified format.

The BCP query/utility can be used to import large numbers of rows into the SQL Server tables or to export data out of tables into data files.

More detail of BCP click here BCP Utility

Create sample table like "Employee or Student" as per your requirement and Add some dummy data into the table.

CREATE TABLE [dbo].[Employee](

[Id] [int] NULL,

[Name] [varchar](50) NULL,

[Salary] [money] NULL,

[Gender] [varchar](10) NULL

) ON [PRIMARY]

Create BCP query to generate sample txt file as follow

USE Sample

DECLARE @FileName varchar(50),

        @bcpCommand varchar(2000)

SET @FileName = REPLACE('G:Export\Export_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM [Sample].[dbo].[Employee]" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -t, -S DESKTOP-2D0R2UP\SQL2014 -U sa -P pass@12 -c'

EXEC master..xp_cmdshell @bcpCommand

in the above bcp query
-t = define comma separated value
-S = define SQL Server Name
U = SQL User Name
-P = SQL Server password

Using BCP query we can export and import large file. This is very helpful in case of any client want to export file having large data.

Result:

Create BCP query to generate sample CSV file as follow

USE Sample

DECLARE @FileName varchar(50),

        @bcpCommand varchar(2000)

SET @FileName = REPLACE('G:Export\Export_'+CONVERT(char(8),GETDATE(),1)+'.csv','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM [Sample].[dbo].[Employee]" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -t, -S DESKTOP-2D0R2UP\SQL2014 -U sa -P Shri -c'

EXEC master..xp_cmdshell @bcpCommand

Result:

We can also integrate the Stored procedure in BCP utility in SQL Server.

 Create sample stored procedure as follow:

Create procedure [dbo].[uspGetEmployee]

AS

BEGIN

SELECT * FROM [Sample].[dbo].[Employee]

END

Create BCP query to integrate the stored procedure for generating CSV file as follow.

USE Sample

DECLARE @FileName varchar(50),

        @bcpCommand varchar(2000)

SET @FileName = REPLACE('G:Export\Export_'+CONVERT(char(8),GETDATE(),1)+'.csv','/','-')

SET @bcpCommand = 'bcp "EXEC Sample..uspGetEmployee" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -t, -S DESKTOP-2D0R2UP\SQL2014 -U sa -P Shri -c'

EXEC master..xp_cmdshell @bcpCommand

Result:

References: 

 BCP Utility from MSDN

I hope you understand the concepts of BCP utility in SQL SERVER. If you like this article then please share this article that will help other people to increase there knowledge.

Thanks for reading.

Don't Forget To:

· Subscribe my YouTube Chanel

· Like the Facebook page.

· Follow on Twitter

· Share with your friends

· Add your valuable comment.