292 lines
11 KiB
Transact-SQL
292 lines
11 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspCloneIntezmenyChangeIds
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspCloneIntezmenyChangeIds
|
|
@pSourceIntezmenyId int
|
|
,@pSourceDatabase nvarchar(60)
|
|
,@pEntityHistoryMigration int
|
|
,@pChangeInTargetDB bit /*1 - Target DB-ben, 0 - SourceDB-ben */
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@parentObjName nvarchar(50)
|
|
,@parentColName nvarchar(50)
|
|
,@refedObjName nvarchar(50)
|
|
,@intezmenyColumnName nvarchar(50)
|
|
,@sql nvarchar(max)
|
|
,@intezmenyId int
|
|
,@databaseToUse nvarchar(60)
|
|
,@tableName nvarchar(50)
|
|
,@columnName nvarchar(50)
|
|
|
|
SET @databaseToUse =
|
|
CASE @pChangeInTargetDB
|
|
WHEN 1 THEN ''
|
|
WHEN 0 THEN @pSourceDatabase + '.dbo.'
|
|
ELSE NULL
|
|
END
|
|
|
|
SET @intezmenyId =
|
|
CASE @pChangeInTargetDB
|
|
WHEN 1 THEN (SELECT t1.[newId] FROM MappingTable t1 WHERE t1.tableName = 'T_INTEZMENY')
|
|
WHEN 0 THEN @pSourceIntezmenyId
|
|
ELSE NULL
|
|
END
|
|
|
|
-- ================================================================================ --
|
|
-- Az ID-k átírása a Foreign key-k alapján --
|
|
-- ================================================================================ --
|
|
DECLARE tbl_cur CURSOR LOCAL FOR
|
|
SELECT
|
|
OBJECT_NAME(pc.[object_id]) AS parent_object_name
|
|
,pc.[name] AS parent_column_name
|
|
,OBJECT_NAME(rc.[object_id]) AS referenced_object_name
|
|
,cc.COLUMN_NAME as intezmeny_column_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
INNER JOIN (SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID') ) cc ON cc.TABLE_NAME = OBJECT_NAME(pc.[object_id])
|
|
WHERE pc.[name] NOT IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID', 'ID') --rc.[name] <> 'ID' AND OBJECT_NAME(rc.[object_id]) <> 'T_INTEZMENY'
|
|
AND rc.[name] NOT IN (N'C_ALTANEVID', N'C_TANEVID')
|
|
AND OBJECT_NAME(pc.[object_id]) <> N'T_ENTITYATTRIBUTEHISTORY'
|
|
|
|
OPEN tbl_cur
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @parentObjName, @parentColName, @refedObjName, @intezmenyColumnName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
SET @sql = N'
|
|
UPDATE x
|
|
SET ' + @parentColName + ' = t.[newId]
|
|
FROM ' + @databaseToUse + @parentObjName + ' x
|
|
INNER JOIN MappingTable t ON t.oldId = x.' + @parentColName + ' AND t.tableName = ''' + @refedObjName + '''
|
|
--WHERE ' + @intezmenyColumnName + ' = @intezmenyId;'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql, N'@intezmenyId int', @intezmenyId
|
|
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @parentObjName, @parentColName, @refedObjName, @intezmenyColumnName
|
|
END
|
|
|
|
CLOSE tbl_cur
|
|
DEALLOCATE tbl_cur
|
|
|
|
-- ================================================================================ --
|
|
-- A CREATOR és a MODIFIER átírása --
|
|
-- ================================================================================ --
|
|
DECLARE tbl_cur CURSOR LOCAL FOR
|
|
SELECT c.TABLE_NAME, c.COLUMN_NAME
|
|
FROM INFORMATION_SCHEMA.COLUMNS c
|
|
INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME AND c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
|
|
WHERE COLUMN_NAME IN ('CREATOR', 'MODIFIER') AND t.TABLE_SCHEMA = 'dbo' AND t.TABLE_TYPE = 'BASE TABLE'
|
|
AND c.TABLE_NAME IN (SELECT tableName FROM MappingTable)
|
|
|
|
OPEN tbl_cur
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName, @columnName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
SET @sql = N'
|
|
UPDATE x
|
|
SET ' + @columnName + ' = t.[newId]
|
|
FROM ' + @databaseToUse + @tableName + ' x
|
|
INNER JOIN MappingTable t ON t.oldId = x.' + @columnName + ' AND t.tableName = ''T_FELHASZNALO'';'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @tableName, @columnName
|
|
END
|
|
|
|
CLOSE tbl_cur
|
|
DEALLOCATE tbl_cur
|
|
|
|
-- ================================================================================ --
|
|
-- FK nincs, de át kell írni az ID-kat --
|
|
-- ================================================================================ --
|
|
SET @sql = N'
|
|
UPDATE o SET
|
|
o.C_CSENGETESIRENDID = newId
|
|
FROM ' + @databaseToUse + 'T_ORARENDIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_CSENGETESIRENDID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_CSENGETESIREND''
|
|
|
|
UPDATE o SET
|
|
o.C_CSENGETESIRENDORAID = newId
|
|
FROM ' + @databaseToUse + 'T_ORARENDIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_CSENGETESIRENDORAID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_CSENGETESIRENDORA''
|
|
|
|
UPDATE o SET
|
|
o.C_CSENGETESIRENDID = newId
|
|
FROM ' + @databaseToUse + 'T_TANITASIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_CSENGETESIRENDID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_CSENGETESIREND''
|
|
|
|
UPDATE o SET
|
|
o.C_CSENGETESIRENDORAID = newId
|
|
FROM ' + @databaseToUse + 'T_TANITASIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_CSENGETESIRENDORAID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_CSENGETESIRENDORA''
|
|
|
|
UPDATE o SET
|
|
o.C_ORARENDIORAGROUPID = newId
|
|
FROM ' + @databaseToUse + 'T_ORARENDIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_ORARENDIORAGROUPID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_ORARENDIORA''
|
|
|
|
UPDATE o SET
|
|
o.C_ORARENDIORAGROUPID = newId
|
|
FROM ' + @databaseToUse + 'T_TANITASIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_ORARENDIORAGROUPID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_ORARENDIORA''
|
|
|
|
UPDATE o SET
|
|
o.C_FOGLALKOZASID = newId
|
|
FROM ' + @databaseToUse + 'T_ORARENDIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_FOGLALKOZASID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_FOGLALKOZAS''
|
|
|
|
UPDATE o SET
|
|
o.C_FOGLALKOZASID = newId
|
|
FROM ' + @databaseToUse + 'T_TANITASIORA o
|
|
INNER JOIN MappingTable t ON t.oldId = o.C_FOGLALKOZASID
|
|
WHERE o.C_INTEZMENYID = @intezmenyId
|
|
AND t.tableName = ''T_FOGLALKOZAS''
|
|
'
|
|
EXEC sp_executesql @sql, N'@intezmenyId int', @intezmenyId = @intezmenyId
|
|
|
|
-- ================================================================================ --
|
|
-- ENTITY HISTORY ID-k átírása --
|
|
-- ================================================================================ --
|
|
IF @pEntityHistoryMigration IN (1, 2) BEGIN
|
|
SET @sql = N'
|
|
UPDATE e
|
|
SET e.C_TANEVID = t.newId
|
|
FROM ' + @databaseToUse + 'T_ENTITYHISTORY e
|
|
INNER JOIN MappingTable t ON t.oldId = e.C_TANEVID AND t.tableName = ''T_TANEV''
|
|
WHERE e.C_INTEZMENYID = @intezmenyId
|
|
|
|
|
|
UPDATE e
|
|
SET e.C_ENTITYID = t.newId
|
|
FROM ' + @databaseToUse + 'T_ENTITYHISTORY e
|
|
INNER JOIN MappingTable t ON t.oldId = e.C_ENTITYID AND t.tableName = e.C_ENTITYNAME
|
|
WHERE e.C_INTEZMENYID = @intezmenyId
|
|
|
|
|
|
UPDATE e
|
|
SET e.C_FELHASZNALOID = t.newId
|
|
FROM ' + @databaseToUse + 'T_ENTITYHISTORY e
|
|
INNER JOIN MappingTable t ON t.oldId = e.C_FELHASZNALOID AND t.tableName = ''T_FELHASZNALO''
|
|
WHERE e.C_INTEZMENYID = @intezmenyId;
|
|
|
|
UPDATE e
|
|
SET e.C_TANEVID = t.newId
|
|
FROM ' + @databaseToUse + 'T_ENTITYHISTORY e
|
|
INNER JOIN MappingTable t ON t.oldId = e.C_TANEVID AND t.tableName = ''T_TANEV''
|
|
WHERE e.C_INTEZMENYID = @intezmenyId;'
|
|
|
|
|
|
|
|
END
|
|
IF @pEntityHistoryMigration IN (2) BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE [name] = 'IX_TMP_001') BEGIN
|
|
SET @sql = N'
|
|
CREATE INDEX IX_TMP_001 ON ' + @databaseToUse + 'T_ENTITYHISTORY (C_ENTITYNAME, C_INTEZMENYID)
|
|
WHERE C_INTEZMENYID = ' + CAST(@intezmenyId AS NVARCHAR(10)) + ';
|
|
|
|
CREATE INDEX IX_TMP_002 ON ' + @databaseToUse + 'T_ENTITYATTRIBUTEHISTORY (C_ENTITYHISTORYID, C_PROPERTYNAME)
|
|
INCLUDE (C_CURRENTVALUE, C_ORIGINALVALUE)
|
|
'
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
DECLARE tbl_cur CURSOR LOCAL FOR
|
|
SELECT
|
|
OBJECT_NAME(pc.[object_id]) AS parent_object_name
|
|
,pc.[name] AS parent_column_name
|
|
,OBJECT_NAME(rc.[object_id]) AS referenced_object_name
|
|
-- ,rc.[name] AS referenced_column_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
WHERE pc.[name] NOT IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID', 'ID') --rc.[name] <> 'ID' AND OBJECT_NAME(rc.[object_id]) <> 'T_INTEZMENY'
|
|
AND rc.[name] NOT IN (N'C_ALTANEVID', N'C_TANEVID')
|
|
AND NOT (OBJECT_NAME(pc.[object_id]) = 'T_FEE' AND pc.[name] = 'C_NAME') -- Tábla - Oszlop kivételek
|
|
AND OBJECT_NAME(rc.[object_id]) IN (SELECT tableName FROM MappingTable)
|
|
ORDER BY OBJECT_NAME(pc.[object_id])
|
|
|
|
OPEN tbl_cur
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @parentObjName, @parentColName, @refedObjName
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
PRINT @parentColName + ' - ' + @parentObjName
|
|
SET @sql = N'
|
|
UPDATE a
|
|
SET a.C_CURRENTVALUE = CAST(t.[newId] AS varchar(30))
|
|
FROM ' + @databaseToUse + 'T_ENTITYATTRIBUTEHISTORY a
|
|
INNER JOIN ' + @databaseToUse + 'T_ENTITYHISTORY e ON a.C_ENTITYHISTORYID = e.ID
|
|
INNER JOIN MappingTable t ON t.oldId = TRY_CAST(NULLIF(a.C_CURRENTVALUE, N''#NULL#'') AS int)
|
|
AND t.tableName = @refedObjName
|
|
AND a.C_PROPERTYNAME = @parentColName
|
|
AND e.C_ENTITYNAME = @parentObjName
|
|
AND e.C_INTEZMENYID = @pIntezmenyId'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql,
|
|
N'@parentObjName nvarchar(50), @parentColName nvarchar(50), @refedObjName nvarchar(50), @pIntezmenyId int',
|
|
@parentObjName, @parentColName, @refedObjName, @intezmenyId
|
|
|
|
SET @sql = N'
|
|
UPDATE a
|
|
SET a.C_ORIGINALVALUE = CAST(t.[newId] AS varchar(30))
|
|
FROM ' + @databaseToUse + 'T_ENTITYATTRIBUTEHISTORY a
|
|
INNER JOIN ' + @databaseToUse + 'T_ENTITYHISTORY e ON a.C_ENTITYHISTORYID = e.ID
|
|
INNER JOIN MappingTable t ON t.oldId = TRY_CAST(NULLIF(a.C_ORIGINALVALUE, N''#NULL#'') AS int)
|
|
AND t.tableName = @refedObjName
|
|
AND a.C_PROPERTYNAME = @parentColName
|
|
AND e.C_ENTITYNAME = @parentObjName
|
|
AND e.C_INTEZMENYID = @pIntezmenyId'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql,
|
|
N'@parentObjName nvarchar(50), @parentColName nvarchar(50), @refedObjName nvarchar(50), @pIntezmenyId int',
|
|
@parentObjName, @parentColName, @refedObjName, @intezmenyId
|
|
|
|
FETCH NEXT FROM tbl_cur
|
|
INTO @parentObjName, @parentColName, @refedObjName
|
|
|
|
SET @sql = N'
|
|
UPDATE e
|
|
SET e.C_TANEVID = t.newId
|
|
FROM ' + @databaseToUse + 'T_ENTITYHISTORY e
|
|
INNER JOIN MappingTable t ON t.oldId = e.C_TANEVID AND t.tableName = ''T_TANEV''
|
|
WHERE e.C_INTEZMENYID = @intezmenyId;'
|
|
|
|
END
|
|
|
|
CLOSE tbl_cur
|
|
DEALLOCATE tbl_cur
|
|
|
|
DROP INDEX IF EXISTS IX_TMP_001 ON T_ENTITYHISTORY
|
|
DROP INDEX IF EXISTS IX_TMP_002 ON T_ENTITYATTRIBUTEHISTORY
|
|
END
|
|
|
|
END
|
|
GO
|
|
|