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

User · Answer

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!

User · Answer

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

User · Answer

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

User · Answer

SELECT     INTO     select   into  lt destination table gt  from  lt source table gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

INSERT INTO ProductPurchaseOrderItems bkp                 OrderId           ProductId           Quantity           Price          SELECT          OrderId           ProductId           Quantity           Price   FROM ProductPurchaseOrderItems     WHERE OrderId 415

[sql] Copy table from one database to another

Examples related to sql

Examples related to sql-server