How to copy a row from one SQL Server table to another

Question

I have two identical tables and need to copy rows from table to another. What is the best way to do that? (I need to programmatically copy just a few rows, I don't need to use the bulk copy utility).

Thanks rp

Answer 1

SELECT * INTO < new_table > FROM < existing_table > WHERE < clause >

Answer 2

As long as there are no identity columns you can just

INSERT Table2
SELECT * FROM Table1
WHERE [Conditions]

Answer 3

Alternative syntax:

INSERT tbl (Col1, Col2, ..., Coln)
  SELECT Col1, Col2, ..., Coln)
  FROM Tbl2
  WHERE ...

The select query can (of course) include expressesions, case statements, constants/literals, etc.

Answer 4

"INSERT INTO DestTable SELECT * FROM SourceTable WHERE ..." works in SQL Server.

Answer 5

Jarrett's answer creates a new table.

Scott's answer inserts into an existing table with the same structure.

You can also insert into a table with different structure:

INSERT Table2
(columnX, columnY)
SELECT column1, column2 FROM Table1
WHERE [Conditions]