Copy table from one server to another
-
I have 2 separate servers lets call them "A" and "B". They both have a databse named "Datagroup", which have all of the same information except for one table. I need to copy the "all_items_master" from database "A" to database "B". After that step I need to update the columns in one table into the existing "all_items_master" table. I am just starting my Database administrator responsibilities and trying to learn more.
Thanks,
Erik Schlegel
-
There are several ways to do this Erik.
1. You can set up a linked server from server A to server B. (Look up linked server in Books Online).
You can then just script the table out, create it on server B, then INSERT using linked server.
2. You could use DTS to do this for you. It will create the table, migrate the data and everything.
3. You could use bcp to bcp out the data, script the table, create it on B, then bcp the data in. Again, you can look up bcp in Books Online.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
I think u can use the DTS to do this for you it will be sufficient and a fast and safe way to do your request.
-
To do the copy from A to B, run the Data Transformation Services wizard. You can open it from the Enterprise Manager. Go down to the tables level of database A. Right click on any of the tables and go to "Export Data...". Or you can also open it from the Start menu under Microsoft SQL Server as "Import and Export Data". It's a typical Microsoft wizard - so if you read carefully for what it's asking for and check all options, you should be fine.
As for the update query, you could probably do a simple UPDATE query in Query Analyzer if the two tables can be joined.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply