Generate CSV Email For Each UserID In Table

Question

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

Answer 1

The following will perform an action similar to a FOREACH cycle:

CREATE TABLE #TableOne (
 UserId VARCHAR(5), 
 SalesNo INT, 
 DateSale DATE, 
 MarkMet BIT);
INSERT INTO #TableOne (
 UserId, 
 SalesNo, 
 DateSale, 
 MarkMet) VALUES
('ec12', 22, '2017-01-01', 1),
('ec12', 4, '2017-01-02', 0),
('ec12', 3, '2017-01-03', 0),
('er11', 30, '2017-01-01', 1),
('er11', 22, '2017-01-02', 1),
('er11', 33, '2017-01-03', 1),
('ss33', 40, '2017-01-02', 1),
('ss33', 5, '2017-01-10', 0);
DECLARE @RowCount INT = (SELECT DISTINCT COUNT(*) UserId FROM #TableOne);
DECLARE @i INT = 1;
DECLARE @UserId VARCHAR(5);
DECLARE @Temp TABLE (id INT, userid VARCHAR(5));
INSERT INTO @Temp (id, userid)
SELECT
 ROW_NUMBER() OVER(ORDER BY t.UserId) AS Id
 ,t.UserId
FROM (
 SELECT DISTINCT
 UserId 
 FROM #TableOne) t;
WHILE (@i <= @RowCount)
BEGIN
 SET @UserId = (SELECT t.userid FROM @Temp t WHERE t.id = @i)
 /************************************************/
 /* Build the logic to be iterated per each user */
 /* (generate CSV, Send Mail, etc) */
 /* */
 /* eg. SELECT * */
 /* FROM #TableOne */
 /* WHERE UserId = @UserId */
 /* */
 /************************************************/
 SET @i = @i + 1;
END

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.

Answer 2

Look into sp_send_dbmail. This sp allows you to email out attachments as CSVs, and if you code up the routine correctly, it should do what you're looking for. Here is an example to walk you through it.

• Smith Stanley: I have used sp_send_dbmail before I just was not sure how to split out the result set of the select statement so that each userID returned gets a seperate .csv file.
• John Eisbrener: You would have to execute a separate sp_send_dbmail call for each CSV you wished to send out, or alternatively write out all the csvs to the file system and then append all of them to one final email via the @file_attachments parameter.