Keeping Data in Temp Tables

By Bill Graziano on 21 August 2000 | Tags: Queries

Chris writes "Hi, I have several tables I would like to select into a temporary table . . . As you might know everytime I try a new Select into the #TempTable loses the data already in it. What can I do to preserve the #TempTable data?"

The full text of the question is "Hi, I have several tables I would like to select into a temporary table.

Example: ---
Select *
Into #TempTable
From a

Select *
Into #TempTable
From b

etc..
------

I also do not know what tables I will be pulling from until runtime. So I have to really do this.

Execute('Select * Into #TempTable From ' + TableName )

As you might know everytime I try a new Select into the #TempTable loses the data already in it.

What can I do to preserve the #TempTable data???"

Chris there are a number of options you can try in this case. First you need to be aware that a SELECT INTO will create new a table. If you continually try to SELECT INTO the same table you will lose your data or the statement will fail. Try doing a SELECT INTO the first time and an INSERT SELECT the second time (see this article).

Another option would be to try a global temporary table. They are prefixed with two # signs (##TempTable) and are not automatically destroyed until all processes that are accessing them have completed. You will need to be careful with this. If your process runs twice at the same time, they will both be accessing the same table. I haven't worked with them much and can't provide much guidance here.

Another solution might be to create tables that you use like temporary tables. You could include an additional field for something like ProcessID. You would need to manually clean them up at the end but it would solve your problem. I'd call that the brute force approach but it will solve the problem. Hope this helps.