CODEWITHSUNDEEP
sqlsql-server
rp.
rp.

Reputation: 17683

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

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

Upvotes: 114

Views: 294237

Answers (6)

Shravya Mutyapu
Shravya Mutyapu

Reputation: 318

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.

Upvotes: 0

Scott Nichols
Scott Nichols

Reputation: 6248

As long as there are no identity columns you can just 

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]

Upvotes: 148

Kaniu
Kaniu

Reputation: 454

INSERT INTO DestTable
SELECT * FROM SourceTable
WHERE ...

works in SQL Server

Upvotes: 6

Michael Haren
Michael Haren

Reputation: 108376

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.

Upvotes: 89

Jarrett Meyer
Jarrett Meyer

Reputation: 19593

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

Upvotes: 7

ScottStonehouse
ScottStonehouse

Reputation: 24965

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]

Upvotes: 46

Related Questions