none
How to copy tables to another database? RRS feed

  • Question

  • I have two database instances on the sql server 2005. I'm using MS SQL Server Management Studio. How do I copy tables from db1 to db2 that is on the same sql server?
    Tuesday, July 17, 2007 1:19 PM

Answers

All replies

  • Using Management Studio (SSMS), in the Object Explorer pane, right click on the database name and select [Tasks], then [Export Data].

     

    Follow the prompts to copy tables from one database to another.

    Tuesday, July 17, 2007 1:56 PM
    Moderator
  • Many thanks for the help. I was able to copy the tables of one database to another database on the same server. The next question is, how do I do the same thing but copying to a database that is on a different sql server? I tried it and I got this error:

     

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The operation could not be completed.

    ------------------------------
    ADDITIONAL INFORMATION:

    Login timeout expired
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (Microsoft SQL Native Client)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Tuesday, July 17, 2007 2:34 PM
  • (I suspect that you are attempting to upload to a Host provider's server.)

     

    However, even if that is an incorrect assumption, it appears that you are not able to connect. Below are some resources for solving connection issues.

     

    Also, you 'may' find that the Database Publishing Wizard is useful. The DPW is designed to create a script file from tables and data, and then that script file can be transferred to the Host using FTP, and executed on the Host sever. I've included a link to the DPW.

     

    Database Publishing Wizard
    http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

     

    Configuration -Configure SQL Server 2005 to allow remote connections
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
    http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

    Configuration -Connect to SQL Express from "downlevel clients"
    http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

    Configuration -Connect to SQL Express and ‘Stay Connected’
    http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

    Configuration - Guideline for Connectivity Question Posting
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

    Tuesday, July 17, 2007 3:02 PM
    Moderator
  • Arnie, thank you so much for your help. I got it working now with the DPW.
    Tuesday, July 17, 2007 3:30 PM
  • I'm trying to do the same.  This is my configuration

    Microsoft SQL Server Management Studio 13.0.16106.4
    Microsoft Analysis Services Client Tools 13.0.1700.441
    Microsoft Data Access Components (MDAC) 10.0.17134.1
    Microsoft MSXML 3.0 6.0 
    Microsoft Internet Explorer 9.11.17134.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 6.3.17134 (Windows 10)

    In the Export wizard, I selected the source database, then the destination database, two different databases.  The wizard page that shows both the source and destination shows the same database for both which is wrong.     

    Friday, January 11, 2019 11:44 PM