Problem
I have two databases: one called foo and the other called bar. I have a table called tblFoobar in foo that I want to migrate from database foo to database bar (data and all). What SQL query should I use to accomplish this?
Asked by RyanKeeter
Solution #1
The “Import Data” job in SQL Server Management Studio (right-click on the DB name, then tasks) will take care of the majority of this for you. Run it from the database where you want the data to be copied.
It will build the tables if they don’t exist, but you’ll probably have to recreate any indexes and whatnot. If the tables already exist, the new data will be appended by default, but you can change this (update mappings) to erase all previous data.
This is something I do all the time and it works very well.
Answered by David
Solution #2
Are you using SQL Server? On the same database server, no less? Use a three-part naming system.
INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar
This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you’ll have to do something else.
Answered by Amy B
Solution #3
This should work:
SELECT *
INTO DestinationDB..MyDestinationTable
FROM SourceDB..MySourceTable
Constraints, defaults, and indexes will not be copied. There will be no clustered index in the table that is constructed.
Alternatively you could:
INSERT INTO DestinationDB..MyDestinationTable
SELECT * FROM SourceDB..MySourceTable
If your destination table is empty and exists.
Answered by leoinfo
Solution #4
If there is only one table, all you need to do is
Other modifications, such as transferring other objects in the future, are something you’ll have to think about. It’s worth noting that the names of your source and destination tables aren’t the same. This means that if you have any dependant objects, such as views, stored procedures, or other, you’ll have to make adjustments as well.
You can go manually with one or more objects without any problems. Third-party comparison tools, on the other hand, come in handy when there are more than a few upgrades. For schema migrations, I now use ApexSQL Diff, but you can’t go wrong with any other tool.
Answered by Igor Voplov
Solution #5
Answered by ScottStonehouse
Post is based on https://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server