Member Avatar for serkan sendur

Copy a table from one database to another database in another ms sql server using sql server management studio :

If your table was in the same ms sql server but in another database, you would copy it using t-sql like this :

SELECT * INTO NewTable FROM existingdb.dbo.existingtable;

However, if your database is in another ms sql server, you can do this :

Connect to your database using management studio. Right click on your table and select Script Table As > Create To > New Query Editor Window

Connect to the other database you want the table to be copied, create a new query and paste the sql previously generated. Run the query and you are done!

  • 5 Contributors
  • 9 Replies
  • 837 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by mrroad
Member Avatar for Kicker73

This is similar to a problem I have. I have a database in SQL Server Express, and need to migrate it to another server running SQL Server 2004. Is there a simple way to do this without having to go through SSIS?

Member Avatar for serkan sendur

You can download the management studio for express edition(free from microsoft) and do the same thing but i am not sure if the management studio for express edition has that functionality

Member Avatar for hollystyles

This is similar to a problem I have. I have a database in SQL Server Express, and need to migrate it to another server running SQL Server 2004. Is there a simple way to do this without having to go through SSIS?

Do a complete backup, copy the file to the other server and restore it there.

Or you can detach the database, copy the .mdf and .ldf files zip them up and copy them over to the new server, unzipp and then attach them.

Member Avatar for Kicker73

I have the Studio Management Express edition, but have not found that functionality. Is it hidden somewhere obtuse?

Member Avatar for Kicker73

I tried backing up the database, and then restoring it into another database on the same server. This did not work and I got an error message informing me that I was trying to restore to the wrong database.

I will try the detach/attach method and see what happens. Thanks

Member Avatar for hollystyles

Oops we posted at same time. When restoring you will have to tweek some options: 1. the name for the database (cos it doesn't exist on the new server yet) just type it in the drop down box. And 2. The paths to the .mdf (data) and .ldf (transaction log) files. Set these in the second tab of the restore dialogue box, no sorry that's Enterprise Manager (Sql 2000) theres a n 'Options' item in the list top left pane.

Member Avatar for Pradeep Jha

i have a database and create new table every day after start of day, i want to transfer new table to other backup database with data.

Member Avatar for mrroad

You could use the Export Wizard, Right click on the DB you wish to source the data from and go to: Tasks => Export Data...

Then follow the steps as laid out in the Export wizard, it makes this task far easier and will wrap it all it a transaction, if you wish; or spit it out to a SQL script for you; or just copy one table to another. You can also customise the mapping if you need to.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.