Temporary Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Temporary Table

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-10-25 : 11:26:02

I am trying to create a temporary table based off of an existing table. How do I create this temp table with the same table definitions and data? Basically, a copy of an existing table...

Existing table = company
Temp table = tempcompany

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-25 : 11:31:00

select *
into #tmp
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-25 : 14:25:18

[code]
SELECT * INTO #tempcompany FROM company
[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-25 : 14:33:12

Ken

That's brilliant!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 06:20:10

beware that
SELECT ... INTO #... FROM ...
will lock Table Create for the duration of the query, and NOT just for the duration of the Table Create.

So you may prefer to keep the locking time shorter by doing something like:


SELECT *
INTO #tempcompany
FROM company
WHERE 1=0

INSERT INTO #tempcompany
SELECT *
FROM company

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 06:27:49

How is

SELECT *
INTO #tempcompany
FROM company
WHERE 1=0

compared to

SELECT top 0 *
INTO #tempcompany
FROM company

?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 06:30:48

Guessing: Risk that TOP 0 actually "collects" records, and then gets the "TOP 0" from that collection?

1=0 will collect nothing, and hopefully SQL Server will take short-circuit to a 0 resulset

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 07:18:49

quote:
Originally posted by Peso

How is

SELECT *
INTO #tempcompany
FROM company
WHERE 1=0

compared to

SELECT top 0 *
INTO #tempcompany
FROM company

?

E 12°55'05.25"
N 56°04'39.16"

Well except the latter wont wont in other RDBMSs

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 07:22:22

quote:
Originally posted by Kristen

Guessing: Risk that TOP 0 actually "collects" records, and then gets the "TOP 0" from that collection?

1=0 will collect nothing, and hopefully SQL Server will take short-circuit to a 0 resulset

Really?
I dont think so
Both are equally faster

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-26 : 07:43:41

quote:
Originally posted by Kristen

beware that
SELECT ... INTO #... FROM ...
will lock Table Create for the duration of the query, and NOT just for the duration of the Table Create.

Nope - it used to.
Think that was changed for v2000 (maybe in an early service pack).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 08:06:01

The only FIX I could find referred to SQL 6.5 Standard Edition SP5a ... sounds like SQL7 is even fixed in this regard.

However I did find this (dunno how relevant, but has a recent date and "SQL2005" reference):

http://www.sql-server-performance.com/tips/t_sql_select_p1.aspx

quote:

If you need to use the SELECT INTO option, keep in mind that it can lock system tables, preventing other users from accessing the data they need while the data is being inserted.

In order to prevent or minimize the problems caused by locked tables, try to schedule the use of SELECT INTO when your SQL Server is less busy. In addition, try to keep the amount of data inserted to a minimum. In some cases, it may be better to perform several, smaller SELECT INTOs instead of performing one large SELECT INTO [6.5, 7.0, 2000, 2005] Updated 1-24-2006

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-26 : 09:05:50

It's wrong - probably an extract from an earlier article which wasn't checked.
It was meant to block if you put the select into in dynamic sql - don't know if that's still the case.

It should also mention that a union can fail if the resultset violates the rules for creating a (work) table but a union all can succeed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources