Transfer data between databases
-
I have two SQL server databases on the same server. The table structure is same except for few. I want to move some records from different tables one database to another. What is the best way to do it? create a DTS package, but I don't know how it supports 'Begin Tran' and the 'Execute SQL Task' in the DTS does not take any parameter (i.e. global variables) in the update and delete statements. If I create a Stored procedure and use the 'Begin Tran' and 'Commit' statement in the procedure. It also does not work for me since the stored procedure keeps on executing the statements that are followed even if any error occurs (i.e. primary key violation etc.). How do I make sure that the statements in the stored procedure are rolled back or procedure exits when any error occurs in it. Thank you for helping me.
-
I have two SQL server databases on the same server. The table structure is same except for few. I want to move some records from different tables one database to another. What is the best way to do it? create a DTS package, but I don't know how it supports 'Begin Tran' and the 'Execute SQL Task' in the DTS does not take any parameter (i.e. global variables) in the update and delete statements. If I create a Stored procedure and use the 'Begin Tran' and 'Commit' statement in the procedure. It also does not work for me since the stored procedure keeps on executing the statements that are followed even if any error occurs (i.e. primary key violation etc.). How do I make sure that the statements in the stored procedure are rolled back or procedure exits when any error occurs in it. Thank you for helping me.
set @errornum = @@error after every sql statement then check for the error number. @@error is reset after every succesful statement so if(@@error = 15) will set @@error = 0. http://www.sommarskog.se/error-handling-I.html A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
set @errornum = @@error after every sql statement then check for the error number. @@error is reset after every succesful statement so if(@@error = 15) will set @@error = 0. http://www.sommarskog.se/error-handling-I.html A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Thank you Ennis. It really works for me. and thank you for the reference URL too.