Merhaba herkese,

Bu yazımda, Merge statement’ı kullanımında bizlere kolaylık sağlayacak scripti paylaşacağım. Merge, hayatımıza SQL Server 2008 ile girmişti. Çok işlevsel olmasına karşın alışkanlıklardan uzak olduğundan olsa gerek pek yaygın kullanılmıyor.

Merge’ün ne yaptığına burada değinmeyeceğim. Bunun için birçok makale mevcut. Fakat özetlemek gerekirse; 2 tablo arasında belli kurallara göre yapılan senkronizasyon ya da upsert işlemlerinde kullanılan bir statement diyebiliriz.

Merge yazanlar da bilecektir. Özellikle kolon sayısı fazla olan tablolarda sorguyu hazırlamak çok zahmetli olabiliyor. Zor olduğundan değil ama uzun ve itici bir işlem.

Bu işlemi hızlandırmak için bir script hazırladım. Bu script sayesinde Merge sorgusu generate edilmiş olacak. Script, tablonun tüm alanlarını çekip, iki tablonun primary key’lerine göre eşleştirme yapıyor. Yani PK kullanımı burada önemli.

Parametre tanımlarını script içinde yaptım. Anlaşılmayan yerleri sorun lütfen.

-- =============================================
-- Author: Ahmet Rende
-- Create date: 2016-01-23
-- Description: Merge statement generator
-- =============================================

DECLARE @sourcedb NVARCHAR(128),
@sourcetable NVARCHAR(128),
@sourceschema NVARCHAR(128),
@targetdb NVARCHAR(128),
@targettable NVARCHAR(128),
@targetschema NVARCHAR(128),
@blocksize INT,
@updatecolumn NVARCHAR(128),
@exculdecolumns NVARCHAR(4000)

/********** PARAMETERS **********/

SET @sourcedb = 'DBA'
SET @sourcetable = 't_source'
SET @sourceschema = 'dbo'
SET @targetdb = 'DBA'
SET @targettable = 't_destination'
SET @targetschema = 'dbo'
SET @blocksize = 1000 -- Büyük tablolarda bu parametreyi kullanamnızı öneririm. Aksi halde TLog dosyası şişer ve tablonuz uzun süre bloklanır.
SET @updatecolumn = 'ModifiedDate' -- Bu parametre, match olan satırların gerçekten update edilip edilmeyeceğine karar vermek için kullanılabilir.
SET @exculdecolumns = NULL -- Harici tutmak istediğimiz kolonları buraya virgül (,) ile ayırarak yazıyoruz.

/********** PARAMETERS **********/

if @targettable IS NULL
SET @targettable = @sourcetable

PRINT 'IF OBJECTPROPERTY(OBJECT_ID(''' + @targettable + '''),''TableHasIdentity'') = 1
SET IDENTITY_INSERT [' + @targetschema + '].[' + @targettable + '] ON;'
PRINT ''

declare @sql varchar(max) = ''
declare @list varchar(max) = '';

SELECT @list = @list + [name] +', '
from sys.columns
where object_id = object_id(@sourcetable)

SELECT @list = @list + [name] +', '
from sys.columns
where object_id = object_id(@sourcetable)

SELECT @list = @list + 's.' + [name] +', '
from sys.columns
where object_id = object_id(@sourcetable)

PRINT 'merge1:'
PRINT ''
----------------------------------------------------------------------------------
PRINT 'MERGE ' + (CASE WHEN (@blocksize IS NOT NULL) THEN 'TOP (' + CAST(@blocksize AS VARCHAR(50)) + ') [' ELSE '[' END) + @targetdb + '].[' + @targetschema + '].[' + @targettable + '] AS t'
PRINT 'USING [' + @sourcedb + '].[' + @sourceschema + '].[' + @sourcetable + '] AS s'

-- Get the join columns ----------------------------------------------------------
SET @list = ''
select @list = @list + 't.[' + c.COLUMN_NAME + '] = s.[' + c.COLUMN_NAME + '] AND '
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @sourcetable
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

SELECT @list = LEFT(@list, LEN(@list) -3)
PRINT 'ON ( ' + @list + ')'

-- WHEN MATCHED ------------------------------------------------------------------
PRINT 'WHEN MATCHED ' + (CASE WHEN (@updatecolumn IS NOT NULL) THEN 'AND t.[' + @updatecolumn + '] <> s.[' + @updatecolumn + ']' ELSE '' END) + '
THEN UPDATE SET'

SELECT @list = '';
SELECT @list = @list + ' t.[' + [name] + '] = s.[' + [name] +'],
'
from sys.columns
where object_id = object_id(@sourcetable)
-- don't update primary keys
and [name] NOT IN (SELECT [column_name]
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @sourcetable
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
-- and don't update identity columns
and columnproperty(object_id(@sourcetable), [name], 'IsIdentity ') = 0
AND name NOT IN (SELECT splitdata COLLATE database_default FROM DBA.dbo.fnSplitString(@exculdecolumns,',') )
--print @list
PRINT left(@list, len(@list) -3 )

-- WHEN NOT MATCHED BY TARGET ------------------------------------------------
PRINT ' WHEN NOT MATCHED BY TARGET THEN';

-- Get the insert list
SET @list = ''

SELECT @list = @list + '[' + [name] +'], '
from sys.columns
where object_id = object_id(@sourcetable)
AND name NOT IN (SELECT splitdata COLLATE database_default FROM DBA.dbo.fnSplitString(@exculdecolumns,','))

SELECT @list = LEFT(@list, LEN(@list) - 1)

PRINT ' INSERT(' + @list + ')'

-- get the values list
SET @list = ''

SELECT @list = @list + ' s.[' +[name] +'],
'
from sys.columns
where object_id = object_id(@sourcetable)
AND name NOT IN (SELECT splitdata COLLATE database_default FROM DBA.dbo.fnSplitString(@exculdecolumns,','))

SELECT @list = LEFT(@list, LEN(@list) - 4)

PRINT ' VALUES(
' + @list + ')'

-- WHEN NOT MATCHED BY SOURCE
PRINT 'WHEN NOT MATCHED BY SOURCE THEN DELETE; '

PRINT ''
PRINT 'IF @@rowcount > 0 GOTO merge1'
PRINT ''

PRINT 'IF OBJECTPROPERTY(OBJECT_ID(''' + @targettable + '''),''TableHasIdentity'') = 1
SET IDENTITY_INSERT [' + @targetschema + '].[' + @targettable + '] OFF;'

GO

Bu script, fnSplitString isimli bir function ile birlikte çalışabilmektedir. Öncesinde bunu create etmelisiniz. Bu function ile vereceğiniz bir stringde ayraç olarak vereceğiniz operatör ile yazıyı tablo şeklinde kullanabilirsiniz.

CREATE FUNCTION [dbo].[fnSplitString] (@string NVARCHAR(MAX), @delimiter CHAR(1))
RETURNS @output TABLE(splitdata NVARCHAR(MAX))
BEGIN
 DECLARE @start INT, @end INT
 SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
 WHILE @start < LEN(@string) + 1 BEGIN
 IF @end = 0
 SET @end = LEN(@string) + 1

 INSERT INTO @output (splitdata)
 VALUES (RTRIM(LTRIM(SUBSTRING(@string, @start, @end - @start))))
 SET @start = @end + 1
 SET @end = CHARINDEX(@delimiter, @string, @start)

 END
 RETURN
END
GO

Scripti yazarken kullandığım tablolara göre oluşan output:

IF OBJECTPROPERTY(OBJECT_ID('t_destination'),'TableHasIdentity') = 1
SET IDENTITY_INSERT [dbo].[t_destination] ON;

merge1:

MERGE TOP (1000) [DBA].[dbo].[t_destination] AS t
USING [DBA].[dbo].[t_source] AS s
ON ( t.[ID] = s.[ID] )
WHEN MATCHED AND t.[ModifiedDate] <> s.[ModifiedDate]
THEN UPDATE SET
t.[Name] = s.[Name],
t.[Surname] = s.[Surname],
t.[Phone] = s.[Phone],
t.[ModifiedDate] = s.[ModifiedDate]
WHEN NOT MATCHED BY TARGET THEN
INSERT([ID], [Name], [Surname], [Phone], [ModifiedDate])
VALUES(
s.[ID],
s.[Name],
s.[Surname],
s.[Phone],
s.[ModifiedDate])
WHEN NOT MATCHED BY SOURCE THEN DELETE;

IF @@rowcount > 0 GOTO merge1

IF OBJECTPROPERTY(OBJECT_ID('t_destination'),'TableHasIdentity') = 1
SET IDENTITY_INSERT [dbo].[t_destination] OFF;

Scripti farklı caseler için değiştirmeniz gerekebilir. Anlaşılmayan yerleri sorun lütfen.
Teşekkürler.