Microsoft® SQL Server® 2012 Bible

301

Chapter 12: Modifying Data In SQL Server

12

The data structure of the newly created table might be less of an exact replication of the

original table structure than expected because the new table structure is based on a com-

bination of the original table and the result set of the SELECT statement. String lengths

and numerical digit lengths may change. If the SELECT...INTO command pulls data from

only one table and the SELECT statement contains no data-type conversion functions, then

there’s a good chance that the table columns and null settings will remain intact. However,

keys, constraints, and indexes will be lost.

SELECT...INTO is a bulk-logged operation, similar to BULK INSERT and BULK COPY. Bulk-

logged operations may enable SQL Server to quickly move data into tables by minimally

recording the bulk-logged operations to the transaction log (depending on the database’s

recovery model). Therefore, the database options and recovery model affect SELECT...INTO

and the other bulk-logged operations.

For more about BULK INSERT and BULK COPY, refer to Chapter 16, “Programming with T-SQL.” For

details on recovery models, refer to Chapter 21, “Backup and Recovery Planning.”

The following code sample demonstrates the SELECT/INTO command as it creates the new

table PersonList by extracting data from Person table in the AdventureWorks database

(some results abridged):

USE AdventureWorks;

-- sample code for setting the bulk-logged behavior

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;

-- the select/into statement

Use AdventureWorks

GO

SELECT BusinessEntityID, LastName, FirstName

INTO PersonList

FROM Person.Person

ORDER BY

LastName, FirstName;

...

The following insert adds a new row to test the identity column created by the SELECT/

INTO:

INSERT PersonList (BusinessEntityID, LastName, FirstName)

VALUES(99999,'LeBlanc', 'Patrick');

To view the data inserted using the SELECT/INTO command and the row that was just

added with the INSERT...VALUES command, the following SELECT statement extracts

data from the PersonList table:

SELECT * FROM PersonList

c12.indd 301c12.indd 301 7/30/2012 4:42:39 PM7/30/2012 4:42:39 PM

http://www.it-ebooks.info