Can I select a set of rows from a table and directly insert that into a table or the same table in SQL?

Question

Hi all I was just curious if I could do something like -

insert into Employee ( Select * from Employee where EmployeeId=1)

I just felt the need to do this a lot of times...so just was curious if there was any way to achieve it..

Answer 1

You can do something like that, but you cannot Select * if you want to change a column value:

Insert into employee ( employeeId, someColumn, someOtherColumn )
  Select 2, someColumn, someOtherColumn
  From employee
  Where employeeId=1

This would insert 2 as your new employeeId.

Answer 2

yes list out the column and do it like that

insert into Employee (EmployeeId, LastName, FirstName......)
Select 600 as EmployeeId, LastName, FirstName......
from Employee where EmployeeId=1

However if EmployeeId is an identity column then you also need to do

set identity_insert employee on 

first and then

set identity_insert employee off 

after you are done

Answer 3

You could use the INSERT INTO ... SELECT ... syntax if the destination table already exists, and you just want to append rows to it. It's easy to check if you are selecting your data by executing just the SELECT part.

Insert into existingTable ( Column1, Column2, Column3, ... )
Select 1, Column2, Column3
From tableName
Where ....

You are not restricted to a simple select, the SELECT statement can be as complex as necessary. Also you do not need to provide names for the selected columns, as they will be provided by the destination table. However, if you have autoincrement columns on the dest table, you can either omit them from the INSERT's column list or use the 'set identity_insert' configuration.

If you want to create a new table from existing data, then you should use the SELECT ... INTO ... syntax

Select 1 as Column1, Column2, Column3
Into newTable
From tableName
Where ....

Again, the select can be arbitrarily complex, but, because the column names are taken from the select statement, all columns must have explicit names. This syntax will give an error if the 'newTable' table already exists. This form is very convenient if you want to make a quick copy of a table to try something.