Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I specify an input sql file with bcp?

How can I specify an input sql file with a long query when using bcp? I tried using the -i option but it keeps complaining about a command-line error with no extra information. Is this possible?

like image 226
Legend Avatar asked May 13 '12 02:05

Legend


6 Answers

I had this problem today and found a convenient workaround, at least in an ad-hoc situation.

Temporary tables can be created by any user with connect permissions. This means you can also create GLOBAL temporary tables.

Just run your query in enterprise manager (or sql cmd or whatever) using SELECT ...INTO with a global temporary table e.g.

SELECT * 
INTO ##mytemptable
FROM SomeTable 
WHERE [massive where clause, for example] 

You can then use the temporary table in the BCP query with a simple

SELECT * FROM ##mytemptable

Then drop the temp table through enterprise manager

DROP TABLE ##mytemptable
like image 58
LarryDavid Avatar answered Oct 24 '22 02:10

LarryDavid


I did other way for fix that.

I create a batch file which read a file and send your content in bcp command. See:

@ECHO off

SETLOCAL EnableDelayedExpansion

SET queryFile=%1
SET outFileName=%2

FOR /F "delims=" %%i IN (%queryFile%) DO SET join=!join! %%i

ECHO %join%

bcp "%join%" queryout %outFileName% /S.\SQLE_CAESAR /d /c /t"|" /T

That script receive two parameters:

  1. Filename which has a query;
  2. Filename for export data;

Execute a script in cmd like that: export-query.bat query.sql export.txt

I hope helped.

like image 31
Rafael Gomes Francisco Avatar answered Oct 24 '22 02:10

Rafael Gomes Francisco


As far as I'm concerned the BCP utility only supports Transact-SQL queries directly written to the command line. Ex:

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c

According to its reference the "-i" option:

Specifies the name of a response file, containing the responses to the command prompt questions for each data field when a bulk copy is being performed using interactive mode (-n, -c, -w, or -N not specified).

Notice that it differs from the sqlcmd Utility "-i" option:

Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order (...)

like image 22
Thomas C. G. de Vilhena Avatar answered Oct 24 '22 04:10

Thomas C. G. de Vilhena


try :

query=$( cat < /file.sql )
export query
bcp "${query}" queryout /home/file.csv
like image 24
Allan Vieira Avatar answered Oct 24 '22 02:10

Allan Vieira


Multi-line queries can be given to bcp easily using powershell:

PS> $query = @'
    select * 
    from <table>
'@
PS> bcp $query queryout <outfile> -d <database> -T -S <server> -c
like image 1
torbiak Avatar answered Oct 24 '22 04:10

torbiak


I had face same issue, may not be a very good approach. However, I did something like the following

bcp "declare @query nvarchar(max)  set @query = (SELECT * FROM OPENROWSET(BULK 'F:\tasks\report_v2.sql', SINGLE_CLOB) AS Contents) exec sp_executesql @query" queryout %outFileName%  /c /C RAW -S . -U sa -P 123 -d blog /T

And I must say, if you use like global temp table then global temp table is dropped itself of after query executed. you can't use this at some situations

like image 1
mdora7 Avatar answered Oct 24 '22 04:10

mdora7