When there is a need to export lumpsome data from your Database to CSV, the export option wont help you out when you have a custom query or bulk data to export.
You can always use BCP tool for bulk copying.
It is real simple to use , I use it in a batch file to get export several csv's at once.
Use the below code and paste in notepad and save it as bat file and replace whatever is in red with your parameters.
@ECHO Doing Something
bcp "query" queryout "filepathWithFilename" -c -t , -S DBServerInstance -U username -P password
pause
@ECHO Exporting Orders Table
bcp "Select * from Shipping.Product inner Join Shipping.Orders on Shipping.Orders.ProductId=Shipping.Product.ProductId" queryout "d:\Orders.csv" -c -t , -S ISQL99999q,77777 -U astro -P astrospassword
pause
Note: Here the Database name is Shipping and the tables Orders, Product joined on ProductId
ISQL99999q,77777 is DB instance Name astro is the username astrospassword is the password
Save it as a bat file and Run.
The output of the file will be the common rows of Table orders and Products saved into D drive with the name Orders.csv
Keep in mind:
You may see that I have used the database name everywhere ie. on the table names and on join condition. This is the way you are supposed to do it. In case you are having schemas then
DatabaseName.SchemaName.TableName.ColumnName - this should be the format.
Also this doesn't work if your query is large/multiline, for which you can refer the post below.
http://codestruggles.blogspot.in/2015/11/exporting-dataresultset-of-query-to-csv.html
You can always use BCP tool for bulk copying.
It is real simple to use , I use it in a batch file to get export several csv's at once.
Use the below code and paste in notepad and save it as bat file and replace whatever is in red with your parameters.
@ECHO Doing Something
bcp "query" queryout "filepathWithFilename" -c -t , -S DBServerInstance -U username -P password
pause
Example:
@ECHO Exporting Orders Table
bcp "Select * from Shipping.Product inner Join Shipping.Orders on Shipping.Orders.ProductId=Shipping.Product.ProductId" queryout "d:\Orders.csv" -c -t , -S ISQL99999q,77777 -U astro -P astrospassword
pause
Note: Here the Database name is Shipping and the tables Orders, Product joined on ProductId
ISQL99999q,77777 is DB instance Name astro is the username astrospassword is the password
Save it as a bat file and Run.
The output of the file will be the common rows of Table orders and Products saved into D drive with the name Orders.csv
Keep in mind:
You may see that I have used the database name everywhere ie. on the table names and on join condition. This is the way you are supposed to do it. In case you are having schemas then
DatabaseName.SchemaName.TableName.ColumnName - this should be the format.
Also this doesn't work if your query is large/multiline, for which you can refer the post below.
http://codestruggles.blogspot.in/2015/11/exporting-dataresultset-of-query-to-csv.html
No comments:
Post a Comment