Transfering data from one database to other | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transfering data from one database to other

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
(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?

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.
What is the code you’re using. Do you use the four part name of the db’s? —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V.

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.