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.
Big Data & SQL 
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!
LikeLike
I am not able to get the output while I am executing this . please help me
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 = 'krishnaData'+''+CONVERT(VARCHAR(8),@MinDate,112)+'.csv'
SET @FilePath = 'C:\Users\U.Krishna\OneDrive – PowerSchool\Desktop\myfiles\files\'+ @FileName
–Fetching the data into a table
SELECT * INTO new 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 new
SET @MinDate = DATEADD(D,1,@MinDate)
END
LikeLike