Creating multiple CSV files using SQL

Question

I have a Table1 below in SQL.

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
1  | Asgard   | John Smith    | A1           |
2  | Asgard   | Andrew Bailey | A2           |
3  | BT       | Tony Saw      | B1           |
4  | BT       | Greg Lee      | B2           |
5  | BT       | Jenny Main    | B3           |
6  | Zurich   | Beth Bond     | Z1           |
7  | Zurich   | Fang Li       | Z2           |
8  | Zurich   | Garry Low     | Z3           |

I need to export this table to .CSV (Microsoft Excel) and split between each provider.

Agard.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
1  | Asgard   | John Smith    | A1           |
2  | Asgard   | Andrew Bailey | A2           |

BT.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
3  | BT       | Tony Saw      | B1           |
4  | BT       | Greg Lee      | B2           |
5  | BT       | Jenny Main    | B3           |

Zurich.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
6  | Zurich   | Beth Bond     | Z1           |
7  | Zurich   | Fang Li       | Z2           |
8  | Zurich   | Garry Low     | Z3           |

I have tried using Foreach Loop Container, however it doesn't distinguish between provider. (btw, I have more than 50 providers to be split into different .csv files).

I'm using SQL Server 2012. Thanks for the help.

Answer 1

You can do this in SQL, you don't need to use SSIS, it will work also, but here is how you can do it in SQL.

First make sure that the 'xp_cmdshell' is enabled.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Using a cursor and the bcp.exe utility you can get the result you want

DECLARE @provider VARCHAR(255), @cmd VARCHAR(512)
DECLARE curs CURSOR FOR
    SELECT DISTINCT Provider FROM Test.dbo.ExportProvider          

OPEN curs
FETCH NEXT FROM curs INTO @provider

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'bcp.exe "SELECT Id, Provider, AdviserName, PolicyNumber FROM Test.dbo.ExportProvider WHERE Provider = ''' + @provider + '''" queryout "C:\Temp\' + @provider + '.csv" -c -t, -C RAW -T -S ' + @@SERVERNAME
    EXECUTE xp_cmdShell @cmd
    FETCH NEXT FROM curs INTO @provider
END

CLOSE curs 
DEALLOCATE curs

This will result in 3 .csv files in the temp folder. Hope this helps.