111

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

Improve this question
1
  • 1
    What's "bulk copy utility"?
    – carloswm85
    Commented Oct 26, 2022 at 11:36

6 Answers 6

Reset to default
144

As long as there are no identity columns you can just 

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]
Improve this answer
3
  • 27
    Be careful with this syntax as it won't work if Table2 has an identity column and it will break in the future if Table1 ever changes without Table2 changing in sync (burned me before). This solution might be perfect for your case, just be aware of these considerations.
    – Michael Haren
    Commented Jul 31, 2009 at 12:00
  • 12
    You can use SET IDENTITY_INSERT < table > ON (and SET IDENTITY_INSERT < table > OFF) in order to temporarily disable the identity column on the table you're trying to insert into. Worked for me trying to restore a few missing records in the middle of dataset.
    – nickb
    Commented Dec 14, 2011 at 11:42
  • 1
    what if the primary key in table1 exists in table2 as a foreign key? I have the same thing, and i don't know what to do.. in table2 id the foreign-key AS "Technology_idTechnology" and in table 1, it is there as "idTechnology" I want to copy all the entries from table1 to table2, when the idTechnology matches in both tables..
    – ZelelB
    Commented Mar 24, 2015 at 15:10
86

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.

Improve this answer
1
  • 2
    This is great when the tables differ slightly. I've a few extra columns in my second table and the accepted answer doesn't work with MSSQL as they need to be identical.
    – Tony M
    Commented May 8, 2015 at 14:24
45

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]
Improve this answer
1
  • 6
    @ScottStonehouse: if you collect all the other answers into this answer like you have done but with code (making it comprehensive), you'd definitely become the best answer.
    – Michael Haren
    Commented Jul 31, 2009 at 12:01
7 
SELECT * INTO < new_table > FROM < existing_table > WHERE < clause >
Improve this answer
3
  • 2
    Is there any way to do this If this two tables are in diff databases in sql-server.
    – Naresh
    Commented Jun 3, 2011 at 17:01
  • 10
    Sure! Just fully qualify the database. [servername].[schema].[table]. For example SELECT * INTO [SQLTEST].[dbo].[EMPLOYEES] FROM [SQLPROD].[dbo].[EMPLOYEES]
    – Jarrett Meyer
    Commented Jun 6, 2011 at 11:07
  • 5
    This creates a new table, which is not what the OP is asking for.
    – Brie
    Commented Feb 4, 2015 at 15:18
6 
INSERT INTO DestTable
SELECT * FROM SourceTable
WHERE ...

works in SQL Server

Improve this answer
2
  • 3
    This works only when DestTable does not exist. You will get an error if DestTable is created before this query.
    – A Web-Developer
    Commented Aug 14, 2015 at 5:46
  • 2
    Actually it fails if the destination table doesn't exist. It may cause errors if the existing DestTable isn't empty.
    – Kaniu
    Commented Apr 13, 2016 at 12:18
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.