We currently have a c# console app that creates a sync table from triggers that allows the app to send an insert update or delete statement to another copy of the database at another site. This keeps the tables in sync, however the system regularly crashes and we do not have the source code.
I am currently trying to recreate this in sql server.
The sync table is generated in a trigger on each table and adds the
TableName Action RowNumer columns_updated
test Insert 10 0x3
test2 Delete 2
test update 15 07x
From this I can generate an insert, update or delete statement which can be run on the remote server, but with thousands of rows it would be far too slow.
Insert server2.test.column1,column2,column3.column4 select column1,column2,colum3,column4 from
server1.test where row = RowNumber
What I would like to do is generate the insert statement on server1, then simply run it on server2
"Insert column1.column2.colum3.column4 into table1 values 110000,New Order, £99.00, 'John Smith'"
So does anybody have a way to write the insert statement to a table row as a string ready for processing in server 2. This select does not have to happen in the trigger.
i.e. read any row in any table and convert it into an insert statement?
