Synchronize Object Table with Base Objects via FKs

Synchronize_T_COR_Objekte.sql

DECLARE @sql nvarchar(MAX); 


DECLARE object_cursor CURSOR FOR 
SELECT 
	N'

;WITH CTE AS 
(
	SELECT 
		 T_COR_Objekte.OBJ_UID 
		,T_COR_Objekte.OBJ_OBJT_Code 
		 
		,T_COR_Objekte.OBJ_DatumVon
		,T_COR_Objekte.OBJ_DatumBis
		 
		,' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumVon AS newDateFrom 
		,' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumBis AS newDateTo 
	FROM T_COR_Objekte
	INNER JOIN ' + REFERENCED_TABLE_NAME + N' ON ' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_UID = T_COR_Objekte.OBJ_' + OBJT_FeldPrfx + N'_UID
	
	WHERE (1=1) 
	AND T_COR_Objekte.OBJ_OBJT_Code = ''' + OBJT_Code + N''' 
	AND 
	( 
		' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumVon <> T_COR_Objekte.OBJ_DatumVon 
		OR 
		' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumBis <> T_COR_Objekte.OBJ_DatumBis 
	)
)
-- SELECT * FROM CTE 
UPDATE CTE SET OBJ_DatumVon = newDateFrom, OBJ_DatumBis = newDateTo
; 
' AS sql 
	-- ,*
FROM 
(
	-- Hier sind alle möglichen Objekte (vorausgesetzt es hat FK) 
	SELECT 
		SUBSTRING
		(
			LEFT
			(
				 FK_COLUMN_NAME
				,LEN(FK_COLUMN_NAME) - 4
			) 
			,5
			,10000 
		) AS OBJT_FeldPrfx 

		,SUBSTRING
		(
			LEFT
			(
				 FK_COLUMN_NAME
				,LEN(FK_COLUMN_NAME) - 4
			) 
			,5
			,10000 
		) AS OBJT_Code 
		 
		,FK_TABLE_SCHEMA
		,FK_TABLE_NAME
		,FK_COLUMN_NAME
		 
		,REFERENCED_TABLE_SCHEMA 
		,REFERENCED_TABLE_NAME
		,REFERENCED_COLUMN_NAME 
	FROM V_DELDATA_ForeignKeyRelations 
	WHERE (1=1) 
	AND FK_TABLE_NAME LIKE 'T_COR_Objekte'
	AND FK_COLUMN_NAME NOT LIKE '%parent%'
	AND FK_COLUMN_NAME LIKE 'OBJ[_]%[_]UID'
	AND FK_COLUMN_NAME NOT LIKE 'OBJ[_]%[_]%[_]UID'
) AS tSource 

OPEN object_cursor  
FETCH NEXT FROM object_cursor INTO @sql 

WHILE @@FETCH_STATUS = 0  
BEGIN  
	-- PRINT @sql; 
	EXECUTE(@sql); 

	FETCH NEXT FROM object_cursor INTO @sql 
END 

CLOSE object_cursor  
DEALLOCATE object_cursor







SELECT 
	N'

;WITH CTE AS 
(
	SELECT 
		 T_COR_Objekte.OBJ_UID 
		,T_COR_Objekte.OBJ_OBJT_Code 
		 
		,T_COR_Objekte.OBJ_DatumVon
		,T_COR_Objekte.OBJ_DatumBis
		 
		,' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumVon AS newDateFrom 
		,' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumBis AS newDateTo 
	FROM dbo.T_COR_Objekte
	INNER JOIN ' + REFERENCED_TABLE_SCHEMA + '.' + REFERENCED_TABLE_NAME + N' ON ' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_UID = T_COR_Objekte.OBJ_' + OBJT_FeldPrfx + N'_UID
	
	WHERE (1=1) 
	AND T_COR_Objekte.OBJ_OBJT_Code = ''' + OBJT_Code + N''' 
	AND 
	( 
		' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumVon <> T_COR_Objekte.OBJ_DatumVon 
		OR 
		' + REFERENCED_TABLE_NAME + N'.' + OBJT_FeldPrfx + N'_DatumBis <> T_COR_Objekte.OBJ_DatumBis 
	)
)
-- SELECT * FROM CTE 
UPDATE CTE SET OBJ_DatumVon = newDateFrom, OBJ_DatumBis = newDateTo
; 
' AS sql 
	-- ,*
FROM 
(
	-- Hier sind alle möglichen Objekte (vorausgesetzt es hat FK) 
	SELECT 
		SUBSTRING
		(
			LEFT
			(
				 FK_COLUMN_NAME
				,LEN(FK_COLUMN_NAME) - 4
			) 
			,5
			,10000 
		) AS OBJT_FeldPrfx 

		,SUBSTRING
		(
			LEFT
			(
				 FK_COLUMN_NAME
				,LEN(FK_COLUMN_NAME) - 4
			) 
			,5
			,10000 
		) AS OBJT_Code 
		 
		,FK_TABLE_SCHEMA
		,FK_TABLE_NAME
		,FK_COLUMN_NAME
		 
		,REFERENCED_TABLE_SCHEMA 
		,REFERENCED_TABLE_NAME
		,REFERENCED_COLUMN_NAME 
	FROM V_DELDATA_ForeignKeyRelations 
	WHERE (1=1) 
	AND FK_TABLE_NAME LIKE 'T_COR_Objekte'
	AND FK_COLUMN_NAME NOT LIKE '%parent%'
	AND FK_COLUMN_NAME LIKE 'OBJ[_]%[_]UID'
	AND FK_COLUMN_NAME NOT LIKE 'OBJ[_]%[_]%[_]UID'
) AS tSource