How to copy a row from one SQL Server table to another
SqlSql ServerSql Problem Overview
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 Solutions
Solution 1 - Sql
As long as there are no identity columns you can just
INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]
Solution 2 - Sql
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.
Solution 3 - Sql
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]
Solution 4 - Sql
SELECT * INTO < new_table > FROM < existing_table > WHERE < clause >
Solution 5 - Sql
INSERT INTO DestTable
SELECT * FROM SourceTable
WHERE ...
works in SQL Server
Solution 6 - Sql
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.