Good day all, I have a unique requirement where I need to generate a .csv file containing data for each userID. The result set that I have returned from my select statement is
userID numOfSales saleDate meetMark
ec12 4 2017-01-02 No
ec12 3 2017-01-03 No
ss33 5 2017-01-10 No
So I would want to generate 2 .csv files one for ec12 and one for ss33 and email those files to an email address.
Below is my sample DDL and query to return the above, how can I do a for each
in SQL Server to generate a .csv file for each userID in the above select statement?
Create Table #bluebell
(
userID varchar(100)
,numOfSales int
,saleDate date
,meetMark varchar(10)
)
Insert Into #bluebell (userID, numOfSales, saleDate, meetMark) Values
('ec12', '22', '20170101', 'Yes'),('ec12', '4', '20170102', 'No'),('ec12', '3', '20170103', 'No')
,('er11', '30', '20170101', 'Yes'), ('er11', '22', '20170103', 'Yes'), ('er11', '33', '20170109', 'Yes')
,('ss33', '40', '20170101', 'Yes'), ('ss33', '5', '20170110', 'No')
Select * FROM #bluebell where meetMark = 'No'
DROP TABLE #bluebell
Best Answer
The following will perform an action similar to a FOREACH cycle:
First it will create a temp table where to store each unique UserId and will assign them a pseudo-uid based on the
ROW_COUNT
, then it will iterate through this newly created table to get the UserId's one by one, at which point you'll be able to execute an action per record/UserId.