Q&A: Can you BCP out a #Temporary table?
Q: If I create a temporary table with:
SELECT OrderID,OrderDate,Total INTO #TempOrders FROM Invoices
... SET @cmd = 'bcp "#TempOrders" out "C:TempOrders.txt" -T EXEC xp_cmdshell 'bcp ' +
Because BCP will create a new connection, it will not be able to see the local temporary table.
To quote from SQL Server BOL - CREATE TABLE:
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
Assuming your query was too complex to use with BCP; you could create a global temp table using the double number sign (##table_name) which can be accessed by BCP if it uses the same credentials as the table creator.
But if you really want to make sure that BCP can access your table, create an actual table in tempdb (SELECT OrderID,OrderDate,Total INTO tempdb.dbo.TempOrders FROM Invoices) then just add a DROP TABLE tempdb.dbo.TempOrderscommand after you BCP.
Or you could get BCP to execute a stored procedure that would create the temporary table and return the results.