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

rp_2536 0
Viewed 261669

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).

sql-server sql
6 Answers

As long as there are no identity columns you can just 

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]
Scott_Nichols_42990

Alternative syntax:

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

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

Michael_Haren_290

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]
ScottStonehouse_23420 
SELECT * INTO < new_table > FROM < existing_table > WHERE < clause >
Jarrett_Meyer_58340 
INSERT INTO DestTable
SELECT * FROM SourceTable
WHERE ...

works in SQL Server

Kaniu_32360

To select only few rows..This will work like charm..

SELECT TOP 10 *
INTO db2.dbo.new_table
FROM db1.dbo.old_table;

Note : Just create a new table in the required db..We need not define its structure.

user_120658370
Related