Insert Into Select sql server

We can insert into a table using Select Statement.

         1.     This can be used to copy data from one table to another table.

         2.     Copy Filtered Data of a Table into another Table in specified columns.

Syntax:

Insert into destination_table_name select * from source_table_name;

Insert into destination_table_name (columns_list) select columns_list from source_table_name;

EXAMPLE:

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

);

CREATE TABLE MST_EMPLOYEE_BACKUP

(

      EMP_ID NUMERIC(18,0),

      EMP_NAME VARCHAR(50),

      DEPARTMENT_NAME VARCHAR(25),

      SALARY NUMERIC(18,2),

      JOINING_DATE DATETIME  

);

INSERT INTO MST_EMPLOYEE_BACKUP SELECT * FROM MST_EMPLOYEE_MASTER;

Or,

INSERT INTO MST_EMPLOYEE_BACKUP(EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE)SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY, JOINING_DATE FROM MST_EMPLOYEE_MASTER;