Copy tables from one database to another in SQL Se

Question

I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?

Answer 1

If there is existing table and we wants to copy only data, we can try this query.

insert into Destination_Existing_Tbl select col1,col2 FROM Source_Tbl

Answer 2

SQL Server Management Studio's "Import Data" task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.

If the tables don't exist it will create them for you, but you'll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.

I use this all the time and it works fairly well.

Answer 3

You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:

• copy the table schema
• any constraints (identity, default values, etc)
• data within the table
• and many other options if needed

Good example workflow for SQL Server 2008 with screen shots shown here.

Answer 4

If it’s one table only then all you need to do is

• Script table definition
• Create new table in another database
• Update rules, indexes, permissions and such
• Import data (several insert into examples are already shown above)

One thing you’ll have to consider is other updates such as migrating other objects in the future. Note that your source and destination tables do not have the same name. This means that you’ll also have to make changes if you dependent objects such as views, stored procedures and other.

Whit one or several objects you can go manually w/o any issues. However, when there are more than just a few updates 3rd party comparison tools come in very handy. Right now I’m using ApexSQL Diff for schema migrations but you can’t go wrong with any other tool out there.

Answer 5

Copy Data

INSERT INTO Alfestonline..url_details(url,[status],recycle) 
SELECT url,status,recycle FROM AlfestonlineOld..url_details

Answer 6

On SQL Server? and on the same database server? Use three part naming.

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.