3

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
3

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.

| improve this answer | |
0

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.

| improve this answer | |
  • 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. – Smith Stanley May 11 '17 at 16:33
  • 1
    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. – John Eisbrener May 11 '17 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.