Is it possible to select a set of rows from a table and directly insert them into a table or the same table in SQL?

Question

Hi everyone, I was just wondering if I can do something like

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

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

Answer 1

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

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

It would stick 2in as new employeeId.

Answer 2

yes specify a list of columns and do it like this

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

, EmployeeId ,

set identity_insert employee on 

,

set identity_insert employee off 

Answer 3

INSERT INTO ... SELECT ..., , . , , SELECT.

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

, SELECT . , . , dest, INSERT, identity_insert '.

, SELECT ... INTO ...

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

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