Keeping Data in Temp Tables

By Bill Graziano on 21 August 2000 | 2 Comments | 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.

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

How to update none Identity Primary Key value (1 Reply)

Subtotal/Total based on outline # (3 Replies)

SQL Server Slow Performance From ASP.NET Web App (3 Replies)

CASE help (4 Replies)

request a simple query for 24 hours. (0 Replies)

query with parameter (4 Replies)

How to recover SQL database without restore? (1 Reply)

tools sql query, auto schedule+ auto save txt file (4 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -