x

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.

Thanks,

Jon

more ▼

asked Dec 30 '09 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.

Example:

Your text file:

1,2,3,4
2,3,4,5
5,6,7,8

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 '09 at 02:42 PM

Rup gravatar image

Rup
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 '09 at 03:03 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 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 '10 at 09:22 AM Pavel Pawlowski
Excellent clarification, I said that badly. Thank you.
Jan 04 '10 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 '12 at 07:27 PM

VegasITDude gravatar image

VegasITDude
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x123
x62
x49

asked: Dec 30 '09 at 02:08 PM

Seen: 6644 times

Last Updated: Mar 09 '12 at 07:33 PM