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.