Synchronize tables

• it anables synchronizing databases without data lost
• it works on mssql server 2008

Code:

PRINT 'Starting [dbo].[OsobaTyp] Syncronization'
 
GO
 
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'xxxSyncxxxOsobaTyp')
	  DROP TABLE [dbo].[xxxSyncxxxOsobaTyp]
GO
 
CREATE TABLE [dbo].[xxxSyncxxxOsobaTyp](
	[ID] [INT] NOT NULL,
	[Ident] [nvarchar](50) NULL,
	[Popis] [nvarchar](255) NULL,
)
GO
 
SET NOCOUNT ON
 
INSERT INTO [dbo].[xxxSyncxxxOsobaTyp]([ID],[Ident],[Popis])
	 VALUES
		   (1,'ADVOKÁT','advokát'),
		   (2,'F','fyzická'),
		   (3,'O','organizace resortu '),
		   (4,'P','právnická'),
		   (5,'SPRÁV_INS','ins. správce'),
		   (6,'PODNIKATEL','podnikatel'),
		   (7,'POLICIE','policie'),
		   (8,'SPRÁVCE_KP','správce konkursní podstaty')
GO
 
SET NOCOUNT OFF
 
GO
 
MERGE [irmon].[dbo].[OsobaTyp] AS Target
USING [irmon].[dbo].[xxxSyncxxxOsobaTyp] AS SOURCE ON (Target.ID = SOURCE.ID)
 
	WHEN MATCHED AND Target.Ident <> SOURCE.Ident OR Target.Popis <> SOURCE.Popis THEN 
		UPDATE SET Target.Ident = SOURCE.Ident, Target.Popis = SOURCE.Popis
 
	WHEN NOT MATCHED BY TARGET THEN
		INSERT(ID, Ident, Popis) VALUES(ID, Ident, Popis)
 
	WHEN NOT MATCHED BY TARGET THEN
                DELETE ...
;
 
GO
 
DROP TABLE [dbo].[xxxSyncxxxOsobaTyp];
 
GO
 
PRINT 'Done Syncronizing [dbo].[OsobaTyp]'
 
GO

• http://blogs.msdn.com/b/bahill/archive/2009/04/01/maintaining-and-synchronizing-your-reference-data.aspx