SQL Server Performance

Transfering data from one database to other

Discussion in 'T-SQL Performance Tuning for Developers' started by pallavi, Sep 27, 2005.

  1. pallavi New Member

    Hello everyone,

    I want to transfer the data from one database to another database.
    But the structure of the databases is different.

    The data one table in the source database, has to be transfered to 3 to 4 tables in destination database.

    There are total 25 tables in source database & I have to transfer the data from all of them to destination database.
    I have written stored procedure to do this thing, but I want to know how to run them with two databses. The stored procedures are working fine if both the databases are same.

    For example the data in Company table in source DB , goes to CompanyMaster, CompanyAddress, CompanyInfo... in destination DB

    Source DB
    Company
    (Code,Name,Address1,Address2,City,State,Decription,noOfEmployees,domain)

    Destination DB
    CompanyMaster (Id, Name, domain)
    CompanyAddress (companyId,address1, address2, city, state)
    CompanyInfo (companyId, Decription, NoOfEmployees)

    The Stored procedure TransfterCompany transfers the data from Company to related tables. The stored procedure workds fine when the source and destination db is same.

    Now i have to make these two db saperate.

    How can I do that?
  2. Adriaan New Member

    Basically, you just add the database name(s) in front of the table name(s) in your script:
    <dbname>.<owner>.<table>

    Obviously the login of the user account under which your SP is running must have sufficient permissions on the remote object, otherwise this will fail.

    If the other db is on a different server that is added as a linked server on your own server, then you can add the server name, like this:
    <servername>.<dbname>.<owner>.<table>

    I think when you set up a linked server then you must specify the remote user account to be used for the connection, and this account should again have sufficient priviliges on the remote object.
  3. FrankKalis Moderator

  4. ranjitjain New Member

    You can even use DTS import/Export wizard to copy specific columns to diffrent tables to diffrent databases.
    Save it in SQL Server as DTS and call / Execute it from your SP you created.

Share This Page