I'm very new to SQL, but I'm coding a C# program that interacts with a database. There's a master database on my computer, and there's a client database on my tablet.
When I re-connect my tablet to the network, I want it to merge the two databases, so that changes made on the tablet are pushed to the master, and changes made to the master are pushed to the client.
Here's my code to initialize the local database on the tablet:
CREATE DATABASE LocalClaytonDB ON (Filename = 'C:\ProgramData\Clayton\Clayton.mdf') , (Filename = 'C:\ProgramData\Clayton\Clayton_log.ldf') FOR ATTACH; GO EXEC sp_addlinkedserver @server='Server'
Here's my code to synchronize the two databases:
-- update the client from the master MERGE [LocalClaytonDB].[dbo].[tableName] trgt using [Server].[Clayton].[dbo].[tableName] src ON trgt.id = src.id WHEN matched AND trgt.lastmodified <= src.lastmodified THEN -- if the master has a row newer than the client -- update the client UPDATE SET trgt.[allColumns] = src.[allColumns], trgt.[id] = src.[id], trgt.[lastmodified] = src.[lastmodified] -- delete any rows added by a client WHEN NOT matched BY source THEN DELETE -- insert any rows added by the master WHEN NOT matched BY target THEN INSERT ( [allColumns], [id], [lastmodified]) VALUES (src. [allColumns], src.[id], src.[lastmodified]); -- now we update the master from the client -- Note: -- because the serverDB is a linked server -- we can't use another MERGE statement, otherwise -- we get the error: "The target of a MERGE statement -- cannot be a remote table, a remote view, or a view over remote tables." UPDATE serverDB SET [allColumns] = [localDB].[allColumns], [id] = [localDB].[id], [lastmodified] = [localDB].[lastmodified] FROM [Server].[Clayton].[dbo].[tableName] serverDB INNER JOIN [LocalClaytonDB].[dbo].[tableName] localDB -- update where the id is the same but the client is newer than the master ON serverDB.id = localDB.id AND localDB.lastmodified >= serverDB.lastmodified
Also, I'm slightly worried about what would happen if this script failed partway through the process, and also just for anything I might be missing.
I'd love it if someone could look over this and point out any shortcomings. As I said, this is the first real SQL I've written and my program is going to have to rely on it.