175 lines
7.5 KiB
Transact-SQL
175 lines
7.5 KiB
Transact-SQL
-- A cél adatbázison kell futtatni.
|
|
-- Üres DB-be másolásnál a @pChangeInTargetDB-nek 1nek kell lenni, ha egyébként meg 0-nak
|
|
|
|
DROP PROCEDURE IF EXISTS dev.uspCloneIntezmeny
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspCloneIntezmeny
|
|
@pSourceIntezmenyId int
|
|
,@pSourceDatabase nvarchar(60)
|
|
,@pEntityHistoryMigration int
|
|
,@pChangeInTargetDB bit /*1 - Target DB-ben, 0 - SourceDB-ben (az eredetileg üresben) */
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@sql nvarchar(max)
|
|
,@sp nvarchar(max)
|
|
-- ============================================================== --
|
|
-- MappingTábla --
|
|
-- ============================================================== --
|
|
EXEC dev.uspCloneIntezmenyCreateMapping
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
|
|
|
|
-- ============================================================== --
|
|
-- TRIGGER, CONSTRAINT kikapcsolás --
|
|
-- ============================================================== --
|
|
SET @sql = ''
|
|
SELECT @sql += N'ALTER TABLE ' + TABLE_NAME + N' NOCHECK CONSTRAINT ALL; PRINT FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss.ff'') + '' - '' + ''' + TABLE_NAME + N''';' + CHAR(13) + CHAR(10)
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = N'dbo'
|
|
AND TABLE_NAME LIKE 'T[_]%'
|
|
AND TABLE_TYPE = N'BASE TABLE'
|
|
AND TABLE_NAME NOT IN(N'T_ENTITYHISTORY', N'T_ENTITYATTRIBUTEHISTORY', N'T_FELHASZNALOBELEPES')
|
|
|
|
SET @sql += N'
|
|
--ALTER TABLE T_FELHASZNALOBELEPES NOCHECK CONSTRAINT PK_T_4025022
|
|
ALTER TABLE T_FELHASZNALOBELEPES NOCHECK CONSTRAINT FK_402502202_402502200
|
|
ALTER TABLE T_FELHASZNALOBELEPES NOCHECK CONSTRAINT FK_402502205_402502203
|
|
ALTER TABLE T_FELHASZNALOBELEPES NOCHECK CONSTRAINT FK_4025031_4025029
|
|
ALTER TABLE T_FELHASZNALOBELEPES NOCHECK CONSTRAINT FK_4025187_4025186
|
|
'
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
|
|
EXEC sp_msforeachtable 'DISABLE TRIGGER ALL ON ?'
|
|
|
|
SET @sql = ''
|
|
SELECT @sql += 'ALTER INDEX ' + [name] + ' ON ' + OBJECT_NAME([object_id]) + ' DISABLE;' + char(13) + char(10)
|
|
FROM sys.indexes
|
|
WHERE is_unique = 1
|
|
AND NAME NOT LIKE 'PK%'
|
|
AND OBJECT_NAME([object_id]) LIKE 'T_%'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
|
|
-- ============================================================== --
|
|
-- CHANGE IN TARGET DB - @pChangeInTargetDB = 1 --
|
|
-- ============================================================== --
|
|
IF @pChangeInTargetDB = 1 BEGIN
|
|
-- Adatok migrálása
|
|
EXEC dev.uspCloneIntezmenyMigration
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
,@pEntityHistoryMigration = @pEntityHistoryMigration /* 0 - Nem kell, 1 - csak migráció, 2 - átID-zás is */
|
|
|
|
EXEC dev.uspCloneIntezmenyChangeIds
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
,@pEntityHistoryMigration = @pEntityHistoryMigration /* 0 - Nem kell, 1 - csak migráció, 2 - átID-zás is */
|
|
,@pChangeInTargetDB = @pChangeInTargetDB /*1 - Target DB-ben, 0 - SourceDB-ben */
|
|
|
|
END
|
|
|
|
-- ============================================================== --
|
|
-- CHANGE IN SOURCE DB - @pChangeInTargetDB = 1 --
|
|
-- ============================================================== --
|
|
IF @pChangeInTargetDB = 0 BEGIN
|
|
|
|
-- ============================================================== --
|
|
-- TRIGGER, CONSTRAINT kikapcsolás a @pSourceDatabase-ben --
|
|
-- ============================================================== --
|
|
SET @sp = @pSourceDatabase + '.dbo.sp_executesql'
|
|
SET @sql = ''
|
|
SELECT @sql += N'ALTER TABLE ' + TABLE_NAME + N' NOCHECK CONSTRAINT ALL; ; PRINT FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss.ff'') + '' - '' + ''' + TABLE_NAME + N''';' + CHAR(13) + CHAR(10)
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = N'dbo'
|
|
AND TABLE_NAME LIKE 'T[_]%'
|
|
AND TABLE_TYPE = N'BASE TABLE'
|
|
EXEC @sp @sql
|
|
|
|
SET @sql = ''
|
|
SET @sql = N'EXEC sp_msforeachtable ''DISABLE TRIGGER ALL ON ?'''
|
|
EXEC @sp @sql
|
|
|
|
SET @sql = ''
|
|
SELECT @sql += 'ALTER INDEX ' + [name] + ' ON ' + OBJECT_NAME([object_id]) + ' DISABLE;' + char(13) + char(10)
|
|
FROM sys.indexes
|
|
WHERE is_unique = 1
|
|
AND NAME NOT LIKE 'PK%'
|
|
AND OBJECT_NAME([object_id]) LIKE 'T_%'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC @sp @sql
|
|
|
|
|
|
EXEC dev.uspCloneIntezmenyChangeIds
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
,@pEntityHistoryMigration = @pEntityHistoryMigration /* 0 - Nem kell, 1 - csak migráció, 2 - átID-zás is */
|
|
,@pChangeInTargetDB = @pChangeInTargetDB /*1 - Target DB-ben, 0 - SourceDB-ben */
|
|
|
|
-- Adatok migrálása
|
|
EXEC dev.uspCloneIntezmenyMigration
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
,@pEntityHistoryMigration = @pEntityHistoryMigration /* 0 - Nem kell, 1 - csak migráció, 2 - átID-zás is */
|
|
|
|
END
|
|
|
|
|
|
-- ============================================================== --
|
|
-- TRIGGER, CONSTRAINT visszakapcsolás --
|
|
-- ============================================================== --
|
|
SET @sql = ''
|
|
SELECT @sql += N'ALTER TABLE ' + TABLE_NAME + N' WITH CHECK CHECK CONSTRAINT ALL; PRINT FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss.ff'') + '' - '' + ''' + TABLE_NAME + N''';' + CHAR(13) + CHAR(10)
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = N'dbo'
|
|
AND TABLE_NAME LIKE 'T[_]%'
|
|
AND TABLE_TYPE = N'BASE TABLE'
|
|
AND TABLE_NAME NOT IN(N'T_ENTITYHISTORY', N'T_ENTITYATTRIBUTEHISTORY', N'T_FELHASZNALOBELEPES')
|
|
--A check constreintek miatt a
|
|
SET @sql += N'
|
|
--ALTER TABLE T_FELHASZNALOBELEPES WITH CHECK CHECK CONSTRAINT PK_T_4025022
|
|
ALTER TABLE T_FELHASZNALOBELEPES WITH CHECK CHECK CONSTRAINT FK_402502202_402502200
|
|
ALTER TABLE T_FELHASZNALOBELEPES WITH CHECK CHECK CONSTRAINT FK_402502205_402502203
|
|
ALTER TABLE T_FELHASZNALOBELEPES WITH CHECK CHECK CONSTRAINT FK_4025031_4025029
|
|
ALTER TABLE T_FELHASZNALOBELEPES WITH CHECK CHECK CONSTRAINT FK_4025187_4025186
|
|
'
|
|
EXEC sp_executesql @sql
|
|
|
|
EXEC sp_msforeachtable 'ENABLE TRIGGER ALL ON ?'
|
|
|
|
SET @sql = ''
|
|
SELECT @sql += 'ALTER INDEX ' + [name] + ' ON ' + OBJECT_NAME([object_id]) + ' REBUILD;' + char(13) + char(10)
|
|
FROM sys.indexes
|
|
WHERE is_unique = 1
|
|
AND NAME NOT LIKE 'PK%'
|
|
AND OBJECT_NAME([object_id]) LIKE 'T_%'
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
|
|
-- ================================================================================ --
|
|
-- A MappingTable mentése a dev schemába --
|
|
-- ================================================================================ --
|
|
DECLARE @newIntezmenyId int
|
|
SET @sql = ''
|
|
|
|
|
|
|
|
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'MappingTable') BEGIN
|
|
SELECT @newIntezmenyId = t1.[newId]
|
|
FROM MappingTable t1
|
|
WHERE t1.tableName = 'T_INTEZMENY'
|
|
|
|
SET @sql = 'SELECT * INTO dev.[MappingTable_' + CAST(@newIntezmenyId AS nvarchar(100)) + '_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmm') + '_' + @pSourceDatabase + '] FROM MappingTable'
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
END
|
|
GO
|
|
|