Q&A: Can you BCP out a #Temporary table?

SQL Server 2005

Q: If I create a temporary table with:

SELECT OrderID,OrderDate,Total
INTO #TempOrders
FROM Invoices
Can I then BCP the data into a file with ?:
...
SET @cmd = 'bcp "#TempOrders" out "C:TempOrders.txt" -T
EXEC xp_cmdshell 'bcp ' +

A: No.

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:

Temporary Tables

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.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Q&A: Can you BCP out a #Temporary table? | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.