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.
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,
— Assigning Values To Variables
SELECT @MinDate = MIN(DateCol) FROM @TestData20
SELECT @MaxDate = MAX(DateCol) FROM @TestData20
–Creating the loop
WHILE @MinDate <= @MaxDate
–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)
The output –
Hope you find this article helpful.
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!