•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 374,006 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,876 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 3478 | Replies: 7
 |
•
•
Join Date: Jan 2008
Location: istanbul
Posts: 264
Reputation: 
Rep Power: 1
Solved Threads: 23
Posting Whiz in Training
Copy a table from one database to another database in another ms sql server using ..
#1
Mar 11th, 2008
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!
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!
Serkan Şendur
MCAD.NET
MCAD.NET
•
•
Join Date: Mar 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Copy a table from one database to another database in another ms sql server using ..
#2
Mar 12th, 2008
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?
•
•
Join Date: Jan 2008
Location: istanbul
Posts: 264
Reputation:
Rep Power: 1
Solved Threads: 23
Posting Whiz in Training
Re: Copy a table from one database to another database in another ms sql server using ..
#3
Mar 13th, 2008
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
Last edited by serkansendur : Mar 13th, 2008 at 3:24 am.
Serkan Şendur
MCAD.NET
MCAD.NET
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,161
Reputation:
Rep Power: 7
Solved Threads: 58
Re: Copy a table from one database to another database in another ms sql server using ..
#4
Mar 13th, 2008
•
•
•
•
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.
•
•
Join Date: Mar 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Copy a table from one database to another database in another ms sql server using ..
#5
Mar 13th, 2008
I have the Studio Management Express edition, but have not found that functionality. Is it hidden somewhere obtuse?
•
•
Join Date: Mar 2008
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Copy a table from one database to another database in another ms sql server using ..
#6
Mar 13th, 2008
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
I will try the detach/attach method and see what happens. Thanks
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,161
Reputation:
Rep Power: 7
Solved Threads: 58
Re: Copy a table from one database to another database in another ms sql server using ..
#7
Mar 13th, 2008
Right click the database -> Tasks -> Back Up...
More info here (quite a way down it looks at SSMSEE in depth) Just CTRL + F for 'Backup'.
http://www.microsoft.com/technet/pro...expwssmse.mspx
More info here (quite a way down it looks at SSMSEE in depth) Just CTRL + F for 'Backup'.
http://www.microsoft.com/technet/pro...expwssmse.mspx
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,161
Reputation:
Rep Power: 7
Solved Threads: 58
Re: Copy a table from one database to another database in another ms sql server using ..
#8
Mar 13th, 2008
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.
Last edited by hollystyles : Mar 13th, 2008 at 9:07 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
•
•
•
•
ajax amd asp avatar backup blue gene business chips copy crash daniweb database design developer development dos economy energy enterprise google hacker hardware ibm intel ibm internet linux medicine memory microsoft msdn news normalization office pc ps3 recession red hat russia security server smtp software sql supercomputer supercomputing survey vista windows working x86
- Access database, (MS Access and FileMaker Pro)
- mysql_fetch_array(): supplied argument is not a valid MySQL (PHP)
- Moving database to new server (MySQL)
- Rundll32 problem (Viruses, Spyware and other Nasties)
- How can i retrieve data after i logod on..... (ASP.NET)
Other Threads in the MS SQL Forum
- Previous Thread: Credit Voucher System With Expiry Periods
- Next Thread: sql - how to
