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.


Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

Select a single row based on conditions in multiple rows (2h)

I want Help Managing Big Data Sets in T SQL Efficiently (14h)

SQL stored procedure to load the error and correct record based on some business rules (23h)

Query is running too long (1d)

Sql Query to check status change of an item (1d)

Can I create differential backups tied to a specifc Full backup instead of the most recent? (7d)

My informix Sql query retruns Null always (8d)

Vehicle availability query (9d)

- Advertisement -