Can I specify an input sql file with bcp?

sql sql-server sql-server-2008 bcp
Legend picture Legend · May 13, 2012 · Viewed 24.1k times · Source

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?

Answer

LarryDavid picture LarryDavid · May 16, 2013

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

Related questions

Where we are giving password by using BCP command?

I am pretty much new to bcp but I researched quite a bit and can not find any resource that says where we are actually sending the user name and password to the database with this command. So everyone can …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More