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

The output –

Hope you find this article helpful.