SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.
The new table will be created, based on the columns in the select list and the rows chosen from the data source.
The format of new table is determined by evaluating the expressions in the select list. The columns in new table are created in the order specified by the select list. Each column in new table has the same name, data type, nullability, and value as the corresponding expression in the select list.
The IDENTITY property of a column is transferred except under the conditions :
•The SELECT statement contains a join, GROUP BY clause, or aggregate function.
•Multiple SELECT statements are joined by using UNION.
•The identity column is listed more than one time in the select list.
•The identity column is part of an expression.
•The identity column is from a remote data source
To create the table in another database on the same instance of SQL Server, specify new_table as a fully qualified name in the form database.schema.table_name.
You cannot create new_table on a remote server; however, you can populate new_table from a remote data source. To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement. Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.
The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.
The new table can be in a different database.
Create table permission should be there so as to execute this command.
The destination table can also be in another database. In such case we use in clause
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
We can also select only columns we need.
SELECT column1,column2
INTO new_table_name [IN externaldatabase]
FROM old_tablename
We can also use where clause to copy only selective data.
SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City=’Kochi’
We can also copy data from multiple tables using join statement.
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
