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';

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;

Insert Into statement in sql

Insert Into statement in sql server:

We can use insert into statement in Sql Server in two ways:

1.Insert in all column of the a table

Syntax: Insert into values(value1, value2, value3,…..)

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)

);

INSERT INTO MST_EMPLOYEE_MASTER VALUES('Shashi','Software Development',50000.00);

(1 row(s) affected)

SELECT * FROM MST_EMPLOYEE_MASTER;

2.Insert in only specified column in a database

Syntax: Insert into (column1, column2, column3) values (value1, value2, value3);

Example:

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

SELECT * FROM MST_EMPLOYEE_MASTER WHERE EMP_NAME='Vikas';