Showing posts with label SQLServer BCP command. Show all posts
Showing posts with label SQLServer BCP command. Show all posts

Thursday, September 10, 2020

Generate multiple CSV data files from SQL

Assume that there are thousands of rows in a table and the rows need to be split and saved in different CSV files based on date or category or status. Have you ever come across such a requirement?

If so, here is the code to accomplish it.


USE
TestDB1
GO

DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME)

INSERT INTO @TestData20 (IntValCol, DateCol) VALUES
(1,'09/05/2020'), (2,'09/05/2020'), (3,'09/06/2020'), (4,'09/06/2020'),
(5,'09/07/2020'), (6,'09/07/2020'), (7,'09/08/2020'), (8,'09/08/2020'),
(9,'09/09/2020'), (10,'09/09/2020'),(11,'09/10/2020'), (12,'09/10/2020') 

-- Declaring Variables
DECLARE @MinDate DATETIME,
              @MaxDate DATETIME,
              @FileName VARCHAR(30),
              @FilePath VARCHAR(100),
              @BCPCommand VARCHAR(4000) 

-- Assigning Values To Variables
SELECT @MinDate = MIN(DateCol) FROM @TestData20
SELECT @MaxDate = MAX(DateCol) FROM @TestData20

--Creating the loop
WHILE @MinDate <= @MaxDate
BEGIN
       --Setting up the filename and filepath
       SET @FileName = 'TempData'+'_'+CONVERT(VARCHAR(8),@MinDate,112)+'.csv'
       SET @FilePath = 'D:\TestDataFolder\'+@FileName

       --Fetching the data into a table
       SELECT * INTO Temp FROM @TestData20 WHERE DateCol = @MinDate 

       --Assigning the BCP statement to the variable
       SET @BCPCommand = 'BCP  TestDB1.dbo.Temp out '+@FilePath+' -T -w -t, '

       --Executing the BCP command through command-shell
       EXEC master..xp_cmdshell @BCPCommand

       --Dropping the table, to utilize it again
       DROP TABLE Tempf

       SET @MinDate = DATEADD(D,1,@MinDate)

END




Hope you find this article helpful.


Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...