0

I would like to export data with BCP

Below is my command

bcp  queryout -i "test.sql" -o"myTable.csv" -S "server\Db" -E /c /t, -T

test.sql has the SQL statemement. I need to keep the SQL in a file as the statement is rather long

I have tested the SQL to be returning values in the management studio

But I get the below errors in the command prompt

Copy direction must be either 'in', 'out' or 'format'.

I have also tried the below variations without much luck 

bcp  out -i "test.sql" -o"myTable.csv" -S "server\Db" -E /c /t, -T
bcp  -i "test.sql" out -o"myTable.csv" -S "server\Db" -E /c /t, -T
bcp  -i "test.sql" queryout -o"myTable.csv" -S "server\Db" -E /c /t, -T
neo-technoker
  • 369
  • 2
  • 8
  • 26
  • can the large query you are storing in a file just be created as a view in SQL Server? I usually create any extract queries I need as views in a SQL Server database. Then the bcp command you are executing can just be "out" and not a "queryout". Just use the new "view" name as the source table in the bcp "out" command. – jamie Oct 14 '19 at 16:06
  • unfotunately I only have read only.. dont have access to create stored procs or views – neo-technoker Oct 14 '19 at 22:31
  • There are still other options. You can (and I would say should) just create the views in another database. A new database even, just for holding views like the one you want to create. You cannot pass a sql query into the BCP command. The -i option is for passing in responses to prompts that the bcp command might make to the user (column data types, sizes, etc...)... it's not for passing in a query. – jamie Oct 14 '19 at 23:16
  • you could also use osql or sqlcmd to execute your sql statements (as in input file to the OS command) and just include a "select...into" in your query. Then BCP out the table. This would be less ideal because you'll be moving the data twice - when you really shouldn't need to, but it could work. – jamie Oct 14 '19 at 23:22

1 Answers1

0

The -i specifies input data file. The query has to be part of the command line in the queryout. The bcp tool is quite ancient and didn't improve over the years much.

You might use different tools to do the expert. BCP shines when you need to import data, but exporting can be done efficiently using many other tools.

To output in csv format you can use sqlcmd see How to export data as CSV format from SQL Server using sqlcmd?

Dr Phil
  • 833
  • 6
  • 18
  • any recommendations? i am trying to export data which then will be uploaded into google drive – neo-technoker Oct 14 '19 at 13:20
  • What format do you want the data to be ? Are you trying to run it on windows? osql has a separator option, or you can use sqlcmd https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd. If you really want to use BCP, you have to reformat the SQL to be single line and to contain no double quotes. Then you can add it as an, albeit ugly, parameter in the command line. – Dr Phil Oct 15 '19 at 23:23
  • I am hoping to get it in CSV. The end goal is to import the data into Google Sheets. I was considering using a direct data connector. But due to industry sensitivity I am not able to get the data connectors before data is deidentified – neo-technoker Oct 21 '19 at 04:39