110

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

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
rp.
  • 17,483
  • 12
  • 63
  • 79

6 Answers6

140

As long as there are no identity columns you can just 

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]
Community
  • 1
  • 1
Scott Nichols
  • 6,108
  • 4
  • 28
  • 23
  • 26
    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 Jul 31 '09 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 Dec 14 '11 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 Mar 24 '15 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.

userx
  • 3,769
  • 1
  • 23
  • 33
Michael Haren
  • 105,752
  • 40
  • 168
  • 205
  • 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 May 08 '15 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]
Earlz
  • 62,085
  • 98
  • 303
  • 499
ScottStonehouse
  • 24,155
  • 7
  • 32
  • 34
  • 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 Jul 31 '09 at 12:01
7
SELECT * INTO < new_table > FROM < existing_table > WHERE < clause >
Earlz
  • 62,085
  • 98
  • 303
  • 499
Jarrett Meyer
  • 19,333
  • 6
  • 58
  • 52
  • 2
    Is there any way to do this If this two tables are in diff databases in sql-server. – Naresh Jun 03 '11 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 Jun 06 '11 at 11:07
  • 5
    This creates a new table, which is not what the OP is asking for. – Conrad Feb 04 '15 at 15:18
6
INSERT INTO DestTable
SELECT * FROM SourceTable
WHERE ...

works in SQL Server

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Kaniu
  • 454
  • 2
  • 5
  • 3
    This works only when DestTable does not exist. You will get an error if DestTable is created before this query. – A Web-Developer Aug 14 '15 at 05:46
  • 2
    Actually it fails if the destination table doesn't exist. It may cause errors if the existing DestTable isn't empty. – Kaniu Apr 13 '16 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.

Shravya Mutyapu
  • 278
  • 2
  • 9