SQL SELECT INTO statement copies data from one table into new table.
The new table will be created with the same column names and data types as defined in the old table .
SYNTAX
SELECT Column_name1, Column_name2, .. INTO new_table FROM old_table WHERE [Condition]
Lets look at an example ofย SELECT INTO SELECT in SQL Server .
Here we have a sample table named as DEPT in database Prod_Db as given below.
Following are the Create table and Insert records into table scripts.
CREATE TABLE DEPT (
DEPT_ID INT NOT NULL,
DEPT_NAME VARCHAR(100),
DEPT_CODE VARCHAR(30))
INSERT INTO DEPT(DEPT_ID, DEPT_NAME, DEPT_CODE)
VALUES( 501, 'IT', 001),
(502, 'FINANCE', 002),
(503, 'HR', 003),
(504, 'MIS', 004),
(505, 'CALL CENTER', 005),
(506, 'SALES', 006)
Suppose you want to insert all the records present in table DEPT into new table named as DEPT_LOG.
Before that just take a look on table DEPT and DEPT_LOG.
SELECT * FROM DEPT
While table DEPT_LOG does not exists in database.
SELECT * FROM DEPT_LOG
As you have seen above , there are six records in DEPT table while DEPT_LOG table does not even exist in database.ย
Now our requirement is that to copy all the data present in DEPT table into DEPT_LOG tableย
Following SQL statement, will create a new table named as DEPT_LOG with same column name and schema as defined in DEPT table and then copy all the data present in DEPT table into table DEPT_LOG table.
SELECT DEPT_ID, DEPT_NAME, DEPT_CODEย
INTO DEPT_LOG
FROM DEPT;
You can see, commands completed successfully and 6 rows are affected.
Lets verify the records in DEPT_LOG table with DEPT table.
SELECT * FROM DEPT
SELECT * FROM DEPT_LOG
As you can see DEPT_LOG table is now exists in database and contains same number of records as DEPT table.
You can also verify whether a table DEPT_Log is created or not in database using object explorer.
If you want to copy only specific records then you can also use WHERE clause in SELECT INTO Statement.
Lets say you want to copy only a data for IT department into new table then you can use WHERE clause to filter the records in table as given below.
SELECT DEPT_ID, DEPT_NAME, DEPT_CODEย
INTO DEPT_LOG
FROM DEPT WHERE DEPT_Name = 'IT'
Copy only table schema using SELECT INTO
In case if you just want to copy the table schema but do not want to copy the data, It can also be done using SELECT INTO statement by specifying the any condition that returns false such as providing a condition 1=0 in WHERE clause.
As 1 can not be equal to 0, and that is false so for this condition no data is returned from a table, which means only a new table is created in database with out copying of the data of table into new table.
Here you can use any condition in WHERE clause but make sure that for given condition query should not return any data other wise new table will be created with data.
Lets create a new table named as DEPT_LOG1 that will have a same schema as DEPT table.
You can see DEPT_LOG1 table does not exist in Database in object explorer.
Lets verify the same using T-SQL statement.
SELECT * FROM DEPT_LOG1
Lets create a table DEPT_LOG1 in database that will have a same schema of DEPT table. But this table will not have any data.
SELECT DEPT_ID, DEPT_NAME, DEPT_CODEย
INTO DEPT_LOG1
FROM DEPT
WHERE 1=0;
Lets verify the DEPT_LOG1 table structure, and you can see table contains same numeber of columns as DEPT table.
Lets select the records from DEPT_Log1 table.
SELECT * FROM DEPT
SELECT * FROM DEPT_LOG1
Now you can see DEPT_LOG1 is created in database without copying the data from DEPT table, only a table schema is copied.
2,178 total views, 1 views today