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.