March 3, 2005 at 6:37 am
Hi,
I am having a database on Server1, now in my application I need to copy data from few tables residing on Server1 to the database residing on Server2, I can do this usin EM , however I need to do this in a stored procedure, the stucture of the database on both servers is always same, however the Source server will remain same but the destination server may change so how do I accomplish this task.
Any help would be highly appreciated.
Regards
Satya.
March 3, 2005 at 8:16 am
Hello Satya,
There are several ways to do what you ask.
One way is to set up a linked server and use a statement something like the following:
insert into tablename (column1, column2) select column1, column2 from servername.databasename.tableowner.tablename
The problem with this solution is that if the servername changes then the stored procedure must also be changed. Dynamic SQL will let you get around this problem.
Another way to solve the problem is to set up a replication environment. In my opinion, replication is more complex than I usually need for low-volume, batch oriented copies.
Hope this helps
Wayne
March 4, 2005 at 8:12 am
A third option is using OPENDATASOURCE or OPENROWSET.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply