To add to Arun's response, SELECT ... INTO blocking on system tables is
mitigated in SQL 7 and above due to row-level locking. To illustrate:
SELECT * INTO MyTable FROM Orders
EXEC sp_help 'Orders' --no blocking
EXEC sp_help --blocked until session 1 COMMIT or ROLLBACK
The overhead of the table create is usually insignificant compared with
the data insert. SELECT ... INTO has the performance advantage of being
minimally logged in the FULL or BULK_LOGGED recovery model.
Hope this helps.
SQL Server MVP
SQL FAQ links (courtesy Neil Pike):
"hector quintanilla" <hquintanillacyberworks.com.mx> wrote in message
news:%238C%230KLRDHA.1988TK2MSFTNGP12.phx.gbl...cause> i am using sql 2k.
> as far as i know, in previous versions doing a select... into couldduration> blocks in sysobjects and maybe on syscomments, sysindexes for theyou> of the whole select statement and then the insert statement. besidestype> had an extra overhead because sql server has to determine the columnthe way> after the select statement is finished.
> is this still true for sql 2k? are there any articles that explains> that select into works in the new version?