August 17, 2006 at 9:16 am
I need help in creating a script for updating two tables called "table1" & "table2" from database1 to an identical database1 on other server in SQL 2000. The user wants an icon created on his desktop, so that he can run the script whenever he wants to update those tables.
Any help would be greatly appreciated.
Thanks,
MK
August 18, 2006 at 8:27 am
Does the "user" actually need to update, or can the table on the "identical database1" be Truncated and repopulated with the information?
The reason I ask is that finding those fields which have been changed is far more complicated logic and from the posting it sounds as if this "user" only needs the two tables to match. Hence, getting rid of all the 'old' data and replacing it with the 'new' data requires much more simple coding...
I wasn't born stupid - I had to study.
August 21, 2006 at 7:13 am
Farrell,
Thanks for your reply.
The user wants to replace the old data with the new data in those two tables.
August 21, 2006 at 7:13 am
Farrell,
Thanks for your reply.
The user wants to replace the old data with the new data in those two tables.
August 21, 2006 at 3:54 pm
I am assuming you are on Server1, (the original database) and you want to replace the data in Table1 and Table2 on Server2, (the client database which contains the copy).
TRUNCATE TABLE Server2.dbname.dbo.Table1
TRUNCATE TABLE Server2.dbname.dbo.Table2
INSERT INTO Server2.dbname.dbo.Table1
SELECT * FROM dbo.Table1
INSERT INTO Server2.dbname.dbo.Table2
SELECT * FROM dbo.Table2
You will need to make sure the "User"" has the persmissions to TRUNCATE TABLE, otherwise, you will need to DELETE the data in Table1 and Table2.
I wasn't born stupid - I had to study.
August 22, 2006 at 11:46 am
Thanks for the reply.
I just found out from the user that, if he adds new records on the tables on server1, the script should just update the tables on server2. Thanks for your help
August 22, 2006 at 12:33 pm
Hey,
OK. So your user now wants to update server 2 automatically when server 1 is updated? I'm assuming he no longer wants to have an icon on his desktop?
If it is to be automatic, then the simple way is to create a linked server between server1 and server2. Create a trigger on tables 1 & 2 on server 1 that automatically update the corrosponding tables on server2. You should be able to build in the logic to handle inserts, updates and deletes with no issue.
If the user wanted to click on an icon, then I would use the code given to you further up and call it using isql from the command line.
Hope that helps,
Clive
August 22, 2006 at 1:02 pm
Clive,
the user doesn't want the automatic update, he would want an icon on his desktop to update the tables on server2.
As I'm a newbee, I would appreciate if you can provide detail instructions.
Thanks a lot for your help.
Mayraj
August 22, 2006 at 1:23 pm
Hi Mayraj,
Not a problem! If all he wants is an exact copy, we can use the code suggested by Farrell above.
You will, however, need a linked server so you can copy the data between servers. If you are unsure how to created a linked server, take a quick look in BOL.
Once you have a linked server, you can save the Farrells code as a .sql file. In Farrells example, where he has Server2, replace that with your linked server name.
Once you have saved the file onto your users PC, create a link and use this command line.
osql /E /i c:\script.sql
osql /U <login name> /P <password> /i c:\script.sql
Use the 1st example if you are using NT Authentication.
I hope that makes sense!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply