SELECT INTO TABLE – INSERT INTO SELECT Inserting data from from one table to another

 

There are two different methods to insert data from one table to another. Although its really useful but there are few limitation in both of below methods. Don't forget to read list of limitations and restrictions below.

1. SELECT INTO

Below method will create table when data is inserted from one table to another table. Its useful when you need exactly same datatype as source table.

 

Use AdventureWorks2008R2;
Go
---Insert data using SELECT INTO
SELECT AddressLine1, City 
INTO BothellAddresses
FROM Person.Address 
where City  = 'Bothell';
GO
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses 
GO

2. INSERT INTO SELECT

Below method will need table to be created prior to inserting data. Its really useful when table is already created and you want insert data from another table.

Use AdventureWorks2008R2;
Go
---Create Table
CREATE TABLE BothellAddresses (AddressLine1 NVARCHAR(60), City NVARCHAR(30))
---Insert into above table using SELECT
INSERT INTO BothellAddresses(AddressLine1, City)
SELECT  AddressLine1, City 
FROM Person.Address 
where City  = 'Bothell';
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses 
GO

*The following limitations and restrictions apply to the INTO clause:

1. You cannot specify a table variable or table-valued parameter as the new table.
   
2. You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
   
3. When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
   
4. SELECT...INTO cannot be used with COMPUTE.
   
5. The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.
   
6. Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you must create them after executing the SELECT...INTO statement.
   
7. Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.

* Source Microsoft

Technorati Tags: Select into,Insert into,Insert,Create Table,SQL,INTO