Export SQL Data to CSV using Batch Scripting

Question

Export SQL Data to CSV using Batch Scripting

venkyzrocks

SSCommitted

Points: 1907
More actions
January 15, 2015 at 3:40 pm

#313753

Hi,
I have thought about doing this for a long time, let me know if it feasible or if there is a better approach.
Execute a pre written SQL on double clicking a batch file which will then export the results to a csv. Anyone has a pre written script I could use ?
Thanks,
V

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004615 More actions · Answer 1

venkyzrocks (1/15/2015)
Hi,
I have thought about doing this for a long time, let me know if it feasible or if there is a better approach.
Execute a pre written SQL on double clicking a batch file which will then export the results to a csv. Anyone has a pre written script I could use ?
Thanks,
V

Have it execute BCP in the "query out" mode and a comma for the delimiter using a trusted connection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

venkyzrocks SSCommitted Points: 1907 More actions · Answer 2

Hi,

Here's the issue I'm having.

1. the bcp output doesn't include headers

2. the sql is too long to be included in one line. How could I call a .sql file from a bcp to execute.

3. I want to pass .bat to the end user and have them execute it themselves.

4. How can I have menu based selections for 1. execute this . 2 execute this ...

Does it make more sense to have a .bat call an SSIS package and output the file to a common network path ?

Jeff Moden SSC Guru Points: 1004615 More actions · Answer 3

venkyzrocks (1/16/2015)
Hi,
Here's the issue I'm having.
1. the bcp output doesn't include headers

So, make some using a separate SELECT UNION ALL

2. the sql is too long to be included in one line. How could I call a .sql file from a bcp to execute.

Write a stored procedure and call it from BCP

3. I want to pass .bat to the end user and have them execute it themselves.

There's nothing preventing that unless the user doesn't have the necessary privs.

4. How can I have menu based selections for 1. execute this . 2 execute this ...

Popup message using VB script or similar.

Does it make more sense to have a .bat call an SSIS package and output the file to a common network path ?

No. It makes much more sense to have schedule jobs do this and email the users with the locations as embedded links.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

venkyzrocks SSCommitted Points: 1907 More actions · Answer 4

Thanks Jeff. i'll work on it and let you know. Thanks again for all your help.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 5

arnipetursson

SSCertifiable

Points: 6557

January 21, 2015 at 10:08 am

#1771913

You could do this in powershell and use Export-Csv.

Export SQL Data to CSV using Batch Scripting

Cookies on SQLServerCentral