Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS65,SS7
Expertise: beginner
Sep 26, 2000

Create temporary or regular table with SELECT INTO

The SELECT INTO statement is a combination of the SELECT and INSERT T-SQL commands, that lets you create a new table from a subset of the rows and/or the columns of another table. The target table of this command is often a temporary table:
SELECT au_fname, au_lname INTO #authors_CA FROM authors WHERE State='CA'
You can also use SELECT INTO to create a new, non-temporary table, but in this case you must explicitly enable it, using the sp_dboption stored procedure:
EXEC sp_dboptions pubs, 'select into/bulkcopy', True
The SELECT INTO statement is very fast, for one reason: the command isn't logged for backup purposes. More precisely, the command can be inside a transaction and any rollback command will correctly undo its effects. However, the new values aren't permanently stored in the log file, therefore after this command you can only perform a complete database backup (incremental backup raise errors). This explains why you have to explicitly enable this functionality for non-temporary tables (temporary tables are never included in backup, so you don't need to use the sp_dboption command before using SELECT INTO with a temporary table).
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap