[sql] Copy table from one database to another

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.

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/

SELECT ... INTO :

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

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

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!

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

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!

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.

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.