change_tracking_script.sql

/*

drop table #source ;
drop table #source_diff;
drop table #source_deleted;

*/


declare @last_synchronization_version bigint;
declare @min_valid_version bigint;

declare @table_target_name varchar(255);
SET @table_target_name = 'change_tracking_target';

declare @table_source_name varchar(255);
SET @table_source_name = 'dbo.change_tracking_data';


declare @mode varchar(10);


SELECT @last_synchronization_version = version 
FROM change_tracking_version
WHERE table_name = @table_target_name;

IF @last_synchronization_version is null 
begin
	raiserror(1500,-1,-1,' tabel target tidak ditemukan di versioning control');
end


select @min_valid_version =  CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(@table_source_name));


-- CASE JIKA harus build ulang 

IF (@last_synchronization_version < @min_valid_version) 
BEGIN

	SET @mode = 'RESET';
	PRINT @mode;
	
	select *
	into #source
	from (
		SELECT a.*,b.sys_change_version FROM change_tracking_data a 
		cross apply 
		CHANGETABLE(VERSION change_tracking_data, (id),(a.id)) b 
	) a ;


	create unique index ix_id on #source(id);


	WITH MySource as (
		SELECT a.* FROM #source a  
	)
	MERGE dbo.change_tracking_target AS MyTarget
	USING MySource
			ON MySource.id = MyTarget.id
	WHEN MATCHED THEN UPDATE SET 
		nama = MySource.nama
	WHEN NOT MATCHED BY TARGET THEN INSERT
		(
			id, 
			nama
		)
		VALUES (
			MySource.id, 
			MySource.nama
		)
	WHEN NOT MATCHED BY SOURCE 
	THEN DELETE ;

	-- UPDATE versi

	SELECT @last_synchronization_version = max(sys_change_version)
	FROM #source;

	UPDATE change_tracking_version
	SET version = @last_synchronization_version
	WHERE table_name =  @table_target_name;

END

-- JIKA TIDAK perlu build ulang 

ELSE 
BEGIN

	SET @mode = 'DIFF';
	PRINT @mode;
	
	-- PROSES yang UPDATE DAN INSERT 

	SELECT * 
	INTO #source_diff
	FROM (
		SELECT  
			p.nama,
			ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_OPERATION,  
			ct.SYS_CHANGE_COLUMNS, ct.SYS_CHANGE_CONTEXT,
			ct.id
		FROM  
			change_tracking_data AS P  
		RIGHT OUTER JOIN  
			CHANGETABLE(CHANGES change_tracking_data, 7) AS CT  
		ON  
			P.id = CT.id  
	) a;


	create unique index ix_id on #source_diff(id);

	IF (SELECT COUNT(*) FROM #source_diff) > 0 
	BEGIN

		WITH MySource as (
			SELECT a.* FROM #source_diff a 
			WHERE a.SYS_CHANGE_OPERATION IN ('U','I')
		)
		MERGE dbo.change_tracking_target AS MyTarget
		USING MySource
				ON MySource.id = MyTarget.id
		WHEN MATCHED THEN UPDATE SET 
			nama = MySource.nama
		WHEN NOT MATCHED BY TARGET THEN INSERT
			(
				id, 
				nama
			)
			VALUES (
				MySource.id, 
				MySource.nama
			) ;

		-- PROSES DATA DELETE

		SELECT * 
		INTO #source_deleted
		FROM (
			SELECT  
				*
			FROM #source_diff a 
			WHERE a.SYS_CHANGE_OPERATION IN ('D')
		) a
		WHERE SYS_CHANGE_OPERATION IN ('D');

		DELETE FROM change_tracking_target
		WHERE id IN (
			SELECT id 
			FROM #source_deleted
		)


		-- UPDATE versi

		SELECT @last_synchronization_version = max(sys_change_version)
		FROM #source_diff;

		UPDATE change_tracking_version
		SET version = @last_synchronization_version
		WHERE table_name =  @table_target_name;
	END

END