• Article

Exporting Data from or Importing Data to a Temporary Table

You can import data into a global or local temporary table and export data from a global temporary table, as follows:

  • Global temporary table (for example, ##groupSales)

    You can bulk import data into a global temporary table by using the bcp utility, BULK INSERT, or INSERT ... SELECT * FROM OPENROWSET(BULK...). You can also use the bcp utility to export data from a global temporary table. When you specify a global temporary table, omit the database name, because temporary tables exist only in tempdb.

  • Local temporary table (for example, #mySales)

    You can bulk import data into a local temporary table by using either BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) only. Local temporary tables are not supported by the bcp utility, so you cannot use bcp to export from or import into a local temporary table.

Examples

A. Using bcp to bulk export from a global temporary table

The following example shows how to use the bcp utility to bulk export data in character format from the global temporary table ##myData to the MyData.txt data file. At the command prompt, enter the following command:

bcp ##myData out MyData.txt -c -T

B. Using BULK INSERT to import to a local temporary table

The following example shows how to use BULK INSERT to bulk import data in character format from the local temporary table #PersonalData to the PersonalData.txt data file.

USE tempdb;
GO
BULK INSERT #PersonalData
   FROM 'C:\PersonalData.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO