How do I insert the contents of a subselect in 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?

Thanks!

Answer 1

Is this what you're looking for?

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

Answer 2

That syntax looks correct, but you the fields have to match exactly otherwise it won't work.

You can specify the fields eg:

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 required columns, as well as fill the data in.