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
