Select Into Statement & Insert Into With Select Statement In SQL

Select Into Statement In SQL Server

• The SELECT INTO statement in SQL server, selects the data from one table and inserts it into a new table.
• Mostly it used to create backups of the tables.
• The SELECT INTO statement in SQL server creates a new table with the help of another existing table.

Sample Tables – SQL Query

create table MyDepartment 
(Id int primary key, 
Dept_Name varchar(50) not null
);

select * from MyDepartment;

insert into MyDepartment values(1,'Accounts');
insert into MyDepartment values(2,'H-R');
insert into MyDepartment values(3,'Administration');
insert into MyDepartment values(4,'Counselling');

drop table MyDepartment;

create table MyEmployees 
(
Emp_Id int primary key, 
Emp_Name varchar(50) not null, 
Gender varchar(50) not null, 
Salary int not null, 
City varchar(50), 
Dept_Id int
);

select * from MyEmployees;

insert into MyEmployees values(100,'Usman','male',25000,'Hyderabad',2);
insert into MyEmployees values(101,'Anas','male',45000,'Karachi',1);
insert into MyEmployees values(102,'Anum','Female',35000,'Sukkur',3);
insert into MyEmployees values(103,'Ali','male',45000,'Hyderabad',4);
insert into MyEmployees values(104,'Asghar','male',35000,'Karachi',2);
insert into MyEmployees values(105,'Osama','male',25000,'Sukkur',1);
insert into MyEmployees values(106,'Asmara','Female',15000,'Hyderabad',3);

drop table MyEmployees;

Example 1: We can copy all the rows and columns from an existing table into a new table.

select * into Employee_Backup 
from MyEmployees;

Example 2: We can copy selected columns into a new table

select Emp_Id,Emp_Name,Salary
into Employee_Backup from MyEmployees;

Example 3: We can copy selected rows into a new table.

select * into Employee_Backup from MyEmployees
where City = 'Hyderabad';

Example 4: Copy columns from 2 or more tables into a new table (using joins), all columns or selected columns.

select A.*, B.Dept_Name into Employee_Backup from
MyEmployees as A
inner join MyDepartment as B
on B.Id = A.Dept_Id; 

Example 5: We can copy data from one database table and the paste it to another database new table

select * into database_name.dbo.Table_Name
from Another_Table_name;

Example:
select * into practice.dbo.MyEmployeesData
from MyEmployees;

Example 6: Copy only column structure to another table not rows

select * into Employee_Backup from MyEmployees
where 1 <> 1;
<> -> Not Equal To

Insert Into With Select Statement In SQL Server

• In SQL, we can copy the data rows from one table to another existing table.
• On the other hand, select into statement can copy the data rows from one table to another new table.
• Copy from source table and paste to target table both actions are executed in a single SQL statement.
• The source table and target table must have similar table definition, (same columns with same data type).
• Any existing rows or records in target table remain un-affected.

Example: We can copy all columns or specific columns from source table to target table.

-- getting all columns in target table
Insert into target_table select * from source_table

-- getting specific columns in target table
Insert into target_table (column1, column2) select column1, column2 from source_table

SQL Query – Insert Into With Select Statement In SQL

create table Khi_Students 
(
S_Name varchar(50), 
Father_Name varchar(50), 
Age int, 
Class int
);
select * from Khi_Students;

drop table Khi_Students;

insert into Khi_Students values('Ali','Ansar',18,9);
insert into Khi_Students values('Usman','Amir',19,10);
insert into Khi_Students values('Osama','Anas',17,9);
insert into Khi_Students values('Asghar','Majeed',20,10);
insert into Khi_Students values('Farhan','Umar',17,9);

create table Hyd_Students 
(
S_Id int identity, 
S_Name varchar(50), 
Father_Name varchar(50), 
Age int, 
Class int
);

select * from Hyd_Students;

drop table Hyd_Students;

insert into Hyd_Students values('Ahmed','Mujtaba',16,8);
insert into Hyd_Students values('Amjad','Ali',17,9);

select * from Khi_Students;
select * from Hyd_Students;

-- getting all columns in target table
insert into Hyd_Students select * from Khi_Students;

-- getting specific columns in target table
insert into Hyd_Students (S_Name,Father_Name)
select S_Name,Father_Name from Khi_Students;