Copy table from one database to another

Question

I've just created an empty database on my machine. I now wish to copy a table from our server database to this local database.

What sql commands do I need to run to do this? I wish to create the new table, copy data from the old table and insert it into the new table.

You could create a SQL INSERT script. See http://stackoverflow.com/questions/7515110/create-sql-insert-script-with-values-gathered-from-table — mhenry1384, Apr 09 '14 at 17:52

score 34 · Accepted Answer · answered Nov 27 '11 at 11:38

34

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

select  *
into    DbName.dbo.NewTable
from    LinkedServer.DbName.dbo.OldTable

This will both create the new table with the same structure as the original one and copy the data over.

answered Nov 27 '11 at 11:38

Andomar

232,371
49
380
404

4

+1 only answer taking into account that those databases are on **different** servers! – marc_s Nov 27 '11 at 11:44
Will not work if you're working with SQL Server 2000 and 2012+ https://connect.microsoft.com/SQLServer/feedback/details/731869/using-sqlncli10-to-create-a-linked-server-to-sql-server-2000-causes-a-fault – sojim2 Nov 08 '16 at 22:41
Solution for SQL Server 2000 and 2012+ here (answer with the highest vote, not the marked answer): http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server – sojim2 Nov 08 '16 at 22:53

score 10 · Answer 2 · answered Nov 27 '11 at 11:38

Assuming that they are in the same server, try this:

SELECT *
INTO SecondDB.TableName
FROM FirstDatabase.TableName

This will create a new table and just copy the data from FirstDatabase.TableName to SecondDB.TableName and won't create foreign keys or indexes.

score 8 · Answer 3 · answered Jul 03 '17 at 16:23

Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio.

You have the choice to export from the source database or import from the destination one in order to transfer the data.

This method is a quick way to copy tables from the source database to the destination one, if you arrange to copy tables having no concern with the tables’ relationships and orders.

When using this method, the tables’ indexes and keys will not be transferred. If you are interested in copying it, you need to generate scripts for these database objects.

If these are Foreign Keys, connecting these tables together, you need to export the data in the correct order, otherwise the export wizard will fail.

Feel free to read more about this method, as well as about some more methods (including generate scripts, SELECT INTO and third party tools) in this article: https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

score 6 · Answer 4 · answered Nov 27 '11 at 11:37

6

SELECT ... INTO :

select * into <destination table> from <source table>

answered Nov 27 '11 at 11:37

aleroot

71,077
30
176
213

score 6 · Answer 5 · edited Apr 26 '16 at 11:30

6

INSERT INTO ProductPurchaseOrderItems_bkp
      (
       [OrderId],
       [ProductId],
       [Quantity],
       [Price]
      )
SELECT 
       [OrderId],
       [ProductId],
       [Quantity],
       [Price] 
FROM ProductPurchaseOrderItems 
   WHERE OrderId=415

edited Apr 26 '16 at 11:30

DhruvJoshi

17,041
6
41
60

answered Dec 12 '11 at 09:54

skpaik

360
5
12

score 5 · Answer 6 · edited Dec 02 '14 at 16:01

If migrating constantly between two databases, then insert into an already existing table structure is a possibility. If so, then:

Use the following syntax:

insert into DESTINATION_DB.dbo.destination_table
select *
  from SOURCE_DB.dbo.source_table
[where x ...]

If migrating a LOT of tables (or tables with foreign keys constraints) I'd recommend:

Generating Scripts with the Advanced option / Types of data to script : Data only OR
Resort using a third party tool.

Hope it helps!

score 2 · Answer 7 · answered Nov 18 '21 at 14:00

To copy the table from one database to another database, first you have to create the exact table structure for the new table as old one, than copy the table entries from one table to another.

Solution for MySQL database

create table [new database.]<new table name> like [old database.]<old table name>;
insert [new database.]<new table name> select * from [old database.]<old table name>;

score 2 · Answer 8 · answered Nov 27 '11 at 11:49

2

Assuming that you want different names for the tables.

If you are using PHPmyadmin you can use their SQL option in the menu. Then you simply copy the SQL-code from the first table and paste it into the new table.

That worked out for me when I was moving from localhost to a webhost. Hope it works for you!

answered Nov 27 '11 at 11:49

ZyteX

239
2
5
13

For very large tables whose SQL codes would be a pain to copy and paste, PHPmyadmin's **Import** and **Export** menus can easily be used as well. – atzol Jul 26 '15 at 12:47

score 1 · Answer 9 · answered Sep 18 '13 at 17:54

1

If you need the entire table structure (not just the basic data layout), use Task>Script Table As>Create To>New Query Window and run in your new database. Then you can copy the data at your leisure.

answered Sep 18 '13 at 17:54

dav

190
1
8
20

score 0 · Answer 10 · answered Nov 27 '11 at 11:39

0

The quickest way is to use a tool, like RazorSQL or Toad for doing this.

answered Nov 27 '11 at 11:39

Alpha01

838
6
13

Copy table from one database to another

10 Answers10