select into create table sql server

Select into table in Sql Server can be used to create a new table from an existing table and insert data from existing table simultaneously.

By using select into table statement we can insert combined data of multiple table into a single table.

Columns created by select into table has same schema of the column into select statement.

Syntax:

Select Column(s) into table2 from table1

Exmple:

CREATE TABLE MST_EMPLOYEE_MASTER

(

      EMP_ID NUMERIC(18,0)IDENTITY(1,1),

      EMP_NAME VARCHAR(50),

      DEPARTMENT_NAME VARCHAR(25),

      SALARY NUMERIC(18,2),

      JOINING_DATE DATETIME

);

SELECT * INTO MST_EMPLOYEE_BACKUP FROM MST_EMPLOYEE_MASTER WHERE JOINING_DATE>='01-JAN-2014';

Or,

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE 

INTO MST_EMPLOYEE_BACKUP FROM MST_EMPLOYEE_MASTER WHERE JOINING_DATE>='01-JAN-2014';