Learn C#.NET, ASP.NET MVC 5,ASP.NET Core that you can increase your knowledge and coding to develop the real-time project.


Monday, July 20, 2020

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: 

I hope you understand the concepts of BCP utility in SQL SERVERIf 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:
· Share with your friends

· Add your valuable comment.
Share:

1 comment:

  1. Programming with ShriJanuary 7, 2021 at 4:05 AM

    Please do not post such kind of post in comment.

    ReplyDelete

Upcoming Articles/Videos

Design Pattern
SOLID Design Principles
Copyright © Programming With Shri | Powered by Shrimant Telgave Home | Disclaimer | Privacy Policy | Terms and Conditions Design by Shrimant Telgave