I have a table in 'my' server. I am connecting to a linked server and inserting data from my table to a table in the remote table via openquery. When the inserts are done I want to truncate my table (in my server). I am doing this in a procedure. I am checking if the linked server is available with sys.sp_testlinkedserver before the inserts. But what will happen if the linked server goes offline or become unavailable while I am inserting. Lets say there are 100 rows in my table and while the 50th row is being inserted to the remote table, the remote server goes offline or something goes wrong. How can I structure this so that I know that all my records are inserted to the remote table and if not all the records are transferred I will not truncate my 'local' table? (Both are SQL servers 2008 r2)
Basically, you would need just one single insert statement, which transfers data from one server to the other.
You don't need "openquery" (what are you doing? Are you writing record by record in a loop? That's not necessary at all, and it will even be very very slow).
Well, your single insert statement is then a transaction. Either this transaction completes as a whole, or it fails. If it fails, you can catch the error, and do the truncate only when the insert is complete.