Answer My Searches

Say you want to take one of your static tables and flip it upside down and backwards by using a temp table, then export it to some file or something using SQL Server 2000’s DTS.

I’m not sure how often this is actually useful, because the reason I have to do this is because i have a 60+ field table that we call a database that has 6 fields that really should be one 2 fields (id & data field) in an another table.

Trick #1
You can’t use a regular temp table. You have to use a global temp table. You can create these just the same as a regular temp table, but you need to use two number signs. oooh, fancy.

--Create a regular temp table
CREATE TABLE #RegularTemp (Column1 varchar(5))
--Create a global temp table
CREATE TABLE ##GlobalTemp (Column1 varchar(5))

Put all your create statements in a SQL Task and then populate the table. This also works if you’re doing a “SELECT INTO” statement.

It’s also a good idea to put a preliminary SQL task to check for the existence of the temp table in case the table already exists when the package is run. That would look something like this

--Check for temp table and delete if it exists
IF object_id('##GlobalTemp') IS NOT NULL
BEGIN
DROP TABLE ##GlobalTemp
END
WARNING: Running this from Query Analyzer won’t work, because you have to name the DB as in…. object_id(tempdb..##GlobalVariable). But that syntax won’t work in DTS, you have to NOT name the database.

Trick #2
This was the hard part to figure out. The DTS Transform task won’t populate the column names or types unless it can find the table somewhere. You can get around this by going to Query Analyzer and creating the temp table. Then return to the DTS package creator, Put in your SELECT * FROM ##GlobalTemp in the Source Query for the Data Transform Task, then move to the Destination tab. Click Define Columns (might not be necessary), Populate from Source, Execute, and you should be set!