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
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.
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/
1 Person is following this question.
