Can I import data from a flat file into a temp table using the import wizard in SSMS (05)?

Can I import data from a flat file into a temp table using the import wizard in SSMS (05)? I just want to bounce some data against it, and don't want to have to generate 40k INSERT statements. (before you ask, no, I don't have BULK INSERT rights even on tempDB, yes, I've asked)

I do have a dev environment that I am using to import into a permanent table, but I don't really need that, it'd be easier to just dump into tempDB so it cleans itself up.



more ▼

asked Dec 30, 2009 at 02:08 PM in Default

Jon Crawford gravatar image

Jon Crawford
44 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can do this by using Global Temporary Tables.


Your text file:


Create a Global Temporary Table that matches your file definition.

Create Table ##temp1 (col1 int, col2 int, col3 int, col4 int);

Now you can use the import wizard to import the file into the global temporary table. Remember to map the data types of the columns correctly.

This is basically the same as creating an SSIS package, but you need a global temporary table so it is accessible to any connection.

more ▼

answered Dec 30, 2009 at 02:42 PM

Rup gravatar image

145 1 1 3

(comments are locked)
10|1200 characters needed characters left

Rup has an excellent answer and that approach is worth looking at. Remember though that a global temporary table will remain until the last connection is removed from the server or it is explicitly dropped. In a lot of busy servers there may very rarely be a time when there are no connections so it will probably need to be explicitly dropped when you are done.

Although it does not use the import wizard, you may want to look at using opendatasource to read the flat file into Sql. This will let you bring it into a local temp table which will clean itself up when you drop that connection. Some general information on reading flat files with opendatasource is available at: http://www.sqlservercentral.com/articles/OpenDataSource/61552/

more ▼

answered Dec 30, 2009 at 03:03 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

Only a note. The global temporary table is dropped automatically when last connection which was referencing it was closed. It doesn't mean, that all connections needs to be closed to automaticly delete the global temp table. Even on bussy server if only two connections used the temp table and those 2 connections will be closed, then the global temp table will be automatically dropped.
Jan 01, 2010 at 09:22 AM Pavel Pawlowski
Excellent clarification, I said that badly. Thank you.
Jan 04, 2010 at 02:25 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

DROP TABLE #TempTable CREATE TABLE #TempTable (TableData1 INT, TableData2 INT)

BULK INSERT #TempTable FROM 'c:\temp\TempTable.csv' WITH ( FIELDTERMINATOR =',', ROWTERMINATOR = '\r\n', FIRSTROW = 2, FORMATFILE = 'c:\temp\TempTable.fmt' )
more ▼

answered Mar 09, 2012 at 07:27 PM

VegasITDude gravatar image

1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 30, 2009 at 02:08 PM

Seen: 9373 times

Last Updated: Mar 09, 2012 at 07:33 PM