How to insert the contents of a subquery into MS SQL Server?

Question

I want to do something like

insert into my table (select * from anothertable where id < 5)

What is the correct MSSQL syntax?

Thank!

Answer 1

Is this what you are looking for?

INSERT INTO MyTable
SELECT * FROM AnotherTable
WHERE AnotherTable.ID < 5

Answer 2

This syntax looks correct, but you must match the fields, otherwise it will not work.

You can specify fields, for example:

INSERT INTO myTable(COL1, COL2, COL3) 
SELECT COL1, COL2, COL3 FROM anotherTable where anotherTable.id < 5

Answer 3

Insert Into MyTable ( Col1, Col2, Col3 ) Select Col1, Col2, Col3 From AnotherTable Where ID < 5

Answer 4

You can also do

select *
into MyTable
from AnotherTable
where ID < 5

which will create MyTable with the necessary columns, as well as fill in the data.