question

Jon Crawford avatar image
Jon Crawford asked

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

ssmstempdbimport-data
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rup avatar image
Rup answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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/

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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.
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Excellent clarification, I said that badly. Thank you.
0 Likes 0 ·
VegasITDude avatar image
VegasITDude answered
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' )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.