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.
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.
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.
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> 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:
Hope it helps!
INSERT INTO ProductPurchaseOrderItems_bkp ( [OrderId], [ProductId], [Quantity], [Price] ) SELECT [OrderId], [ProductId], [Quantity], [Price] FROM ProductPurchaseOrderItems WHERE OrderId=415 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>; 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.
insert into newdatabase.dbo.table select * from currenttable 
The quickest way is to use a tool, like RazorSQL or Toad for doing this.
This one line would be sufficient to create as well ass insert the data from the table of other database
create table as select * from .;
