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
See Also
Concepts
Importing and Exporting Bulk Data by Using the bcp Utility
Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)
Creating and Modifying Table Basics
Scenarios for Bulk Importing and Exporting Data
Other Resources
OPENROWSET (Transact-SQL)
BULK INSERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
bcp Utility