Select * Into Statement in SQL

Select * Into Statement  in SQL

Select * or Select Columns Name Create a Target Table with the query you specified Mostly Use when you want to create a copy of a table or you want to insert the Output of a query into a Table
Let me Explain Through an Example
--creates a table

create table Employee

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

Now you want to copy this Employee table into a New Table CopyEmployee

for this You have to use Select * into

select * into CopyEmployee from Employee

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

Now you want to put some result into a new table mean after joining table employee and copyemployee we want the result set in MergeEmployee Table for this

Select c.Empid as CEmpId,E.EmpId,C.EmpName as CEmpName,E.EmpName into MergeEmployee from employee e join

copyemployee c on e.empid=c.empid

select * from MergeEmployee 

Output

CEmpId EmpId CEmpName EmpName

1 1                 Amit                    Amit

2 2               Sumit                   Sumit

3 3               Raj                Raj

4 4               vijay               vijay

5 5               suresh     suresh

The Reason why i used  column alias because a table cannot have two column with same name in order to differentiate the column i used Column Alias

Note:Select into statement will copy the data only not keys,constraints and other things.