SQL server - Sync two SQL tables with same schema

Sync data in two tables with same schema in generic way explained here.

Recently I had a requirement in one of our project that need to sync two different database with few common master data tables. Since I don't have control  in source database, I could not use replication or trigger concept. So I got this logic doing Google, and customized it for insert and update records.

Here I have used MERGE statement for SQL 2008 and as well as insert and update statements for SQL 2005.

Here I am taking two tables cities, cities1 with same schema as

CREATE TABLE [dbo].[Cities](

      [CityId] [int] IDENTITY(1,1) NOT NULL,

      [CityName] [varchar](50) NULL,

)

CREATE TABLE [dbo].[Cities1](

      [CityId] [int] IDENTITY(1,1) NOT NULL,

      [CityName] [varchar](50) NULL,

)

INSERT INTO [dbo].[Cities] ([CityName]) VALUES ('Chennai')

INSERT INTO [dbo].[Cities] ([CityName]) VALUES ('Mumbai')

INSERT INTO [dbo].[Cities] ([CityName]) VALUES ('Bangalore') 

INSERT INTO [dbo].[Cities1] ([CityName]) VALUES ('Chennai')

INSERT INTO [dbo].[Cities1] ([CityName]) VALUES ('Bangalore')

So I am going to insert and update whatever in [dbo].[Cities] table to [dbo].[Cities1] table by giving table names and primary key fields of each table.
Logic behind this is generate SQL statement and execute it.

DECLARE @currentTableName sysname

DECLARE @newTableName sysname

DECLARE @currentSchemaName sysname

DECLARE @newSchemaName sysname

DECLARE @CurrentKey sysname

DECLARE @NewSchemaKey sysname

DECLARE @sqlcmd varchar(max)

DECLARE @colname sysname

DECLARE @collist varchar(max)

DECLARE @Valcollist varchar(max)

DECLARE @UpdateCollist varchar(max)

SET @currentTableName = 'Cities'

SET @newTableName = 'Cities1'

SET @currentSchemaName = 'dbo'

SET @newSchemaName = 'dbo'

SET @CurrentKey = 'CityId'

SET @NewSchemaKey = 'CityId'

SET @collist = ''

SET @Valcollist = ''

SET @UpdateCollist =''

DECLARE colCursor CURSOR FOR

select c.name from sys.all_columns c

inner join sys.all_objects o

on c.object_id = o.object_id

where o.type = 'U'

and o.name = @currentTableName

and o.schema_id = schema_id(@currentSchemaName)

ORDER BY column_id

OPEN colCursor  

FETCH NEXT FROM colCursor INTO @colname  

WHILE @@FETCH_STATUS = 0  

BEGIN  

       SET @collist = @collist + '[' + @colname + ']'

       SET @Valcollist = @Valcollist + 'S.[' + @colname + ']'

       SET @UpdateCollist = @UpdateCollist + 'D.[' + @colname + '] = S.['+@colname+']'

      

       FETCH NEXT FROM colCursor INTO @colname  

       IF @@FETCH_STATUS = 0

       BEGIN

               SET @collist = @collist + ','

               SET @UpdateCollist = @UpdateCollist + ','

               SET @Valcollist = @Valcollist + ','

         END

END  

CLOSE colCursor  

DEALLOCATE colCursor

SET @sqlcmd = 'SET IDENTITY_INSERT [' + @newSchemaName + '].[' +

@newTableName + '] ON;'

----- SQL 2008

--SET @sqlcmd = @sqlcmd + 'MERGE INTO ['+@newTableName+']  As D

--USING ['+@currentTableName+'] As S

--ON D.['+ @NewSchemaKey+']CityId = S.['+@CurrentKey+']

--WHEN MATCHED Then Update

--                Set '+@UpdateCollist+'

--WHEN NOT MATCHED then

--          Insert ('+@collist+')

--          Values ('+@Valcollist+');'

-- SQL 2005

-- UPDATE

SET @sqlcmd = @sqlcmd +

' UPDATE ['+@newTableName+'] SET ' +@UpdateCollist+'

FROM ['+@currentTableName+'] WHERE

['+@currentTableName+'].['+@CurrentKey+'] = ['+@newTableName+'].['+@NewSchemaKey+'];'

---- Insert

SET @sqlcmd = @sqlcmd +

'INSERT INTO [' + @newSchemaName + '].[' + @newTableName + '] ('

+ @collist +

') SELECT ' + @collist +

' FROM [' + @currentSchemaName + '].[' + @currentTableName + ']

WHERE [' + @currentTableName + '].[' + @CurrentKey + '] NOT IN

(SELECT [' + @newTableName + '].[' + @NewSchemaKey + '] FROM [' + @newTableName + ']);'

SET @sqlcmd = @sqlcmd + 'SET IDENTITY_INSERT [' + @newSchemaName +

'].[' + @newTableName + '] OFF;'

--EXEC (@sqlcmd)

PRINT @sqlcmd

Thank you. Enjoy coding.