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

Capture

The output –

files list

 

Hope you find this article helpful.

 

2 comments

  1. Have you ever thought about adding a little bit more than just your articles?
    I mean, what you say is important and all. Nevertheless think about if
    you added some great pictures or video clips to give your posts more, “pop”!
    Your content is excellent but with images and clips, this blog could certainly
    be one of the greatest in its niche. Superb blog!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s