-----------------------------------------------------------------------------------------------------------------------
--선행작업으로 가동DB의 TABLE을 개발DB에 생성해야 합니다.
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
--트리거를 DISABLE 시킵니다.
-----------------------------------------------------------------------------------------------------------------------
SELECT
'DISABLE TRIGGER '+a.NAME+' ON '+b.name+';'
FROM
sys.sysobjects a
inner join sys.sysobjects b
on a.parent_obj = b.id
WHERE
a.xtype = 'TR'
ORDER BY a.xtype, a.name;
DISABLE TRIGGER EAIT_INSERT_GSI_0013_GEM ON GEMTB_NPayment;
DISABLE TRIGGER EAIT_INSERT_GSI_0014_GEM ON GEMTB_AVendorMaster;
DISABLE TRIGGER EAIT_INSERT_GSI_0015_GEM ON GEMTB_BCtrtMaster;
DISABLE TRIGGER EAIT_INSERT_GSI_0017_GEM ON GEMTB_BProjectSumm;
DISABLE TRIGGER EAIT_INSERT_GSI_0018_GEM ON GEMTB_OEndSchdCostMgm;
DISABLE TRIGGER EAIT_INSERT_GSI_0019_GEM ON GEMTB_DPerformBudgetBasc;
DISABLE TRIGGER EAIT_INSERT_GSI_0024_GEM ON GEMTB_BContractUnitSumm;
DISABLE TRIGGER EAIT_UPDATE_GSI_0013_GEM ON GEMTB_NPayment;
DISABLE TRIGGER EAIT_UPDATE_GSI_0014_GEM ON GEMTB_AVendorMaster;
DISABLE TRIGGER EAIT_UPDATE_GSI_0015_GEM ON GEMTB_BCtrtMaster;
DISABLE TRIGGER EAIT_UPDATE_GSI_0016_GEM ON XXEAI_TRX_HEADER_II;
DISABLE TRIGGER EAIT_UPDATE_GSI_0017_GEM ON GEMTB_BProjectSumm;
DISABLE TRIGGER EAIT_UPDATE_GSI_0018_GEM ON GEMTB_OEndSchdCostMgm;
DISABLE TRIGGER EAIT_UPDATE_GSI_0019_GEM ON GEMTB_DPerformBudgetBasc;
DISABLE TRIGGER EAIT_UPDATE_GSI_0024_GEM ON GEMTB_BContractUnitSumm;
DISABLE TRIGGER EAIT_UPDATE_GSI_0025_GEM ON GEMTB_NPayment;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
--시작 : 가동DB의 TABLE DATA를 개발DB로 이행 합니다.
-----------------------------------------------------------------------------------------------------------------------
-- 테이블 목록 정의
DECLARE @TableInfo TABLE(
TableName varchar(1000)
);
-- 테이블별 컬럼 목록 정의
DECLARE @ColumnInfo TABLE(
TableName varchar(1000)
, ColumnName varchar(1000)
);
INSERT INTO @TableInfo
SELECT
A.NAME AS TableName
FROM
OPENDATASOURCE('SQLOLEDB','SERVER=100.44.44.44;UID=user;PWD=pw').GPM.SYS.OBJECTS A
WHERE
A.TYPE = 'U'
--AND A.NAME LIKE 'GEMTB%'
--AND A.NAME NOT IN ('GEMTB_ZBoard','GEMTB_ZBoardattachments','GEMTB_ZBoardcontent','GEMTB_ZSMRqstInfo')
INSERT INTO @ColumnInfo
SELECT
TBL.NAME TableName
, COL.NAME ColumnName
FROM
OPENDATASOURCE('SQLOLEDB','SERVER=100.44.44.44;UID=user;PWD=pw').GPM.SYS.SYSOBJECTS TBL
INNER JOIN OPENDATASOURCE('SQLOLEDB','SERVER=100.44.44.44;UID=user;PWD=pw').GPM.SYS.SYSCOLUMNS COL
ON COL.ID = TBL.ID
INNER JOIN @TableInfo SEL
ON SEL.TableName = TBL.NAME
WHERE
TBL.XTYPE = 'U'
--SELECT * FROM @TableInfo
--RETURN
DECLARE
@TableName VARCHAR(1000)
, @T_COLUMN_COLLER VARCHAR(8000) = ''
, @T_START INT = 0
, @T_ColumnName VARCHAR(1000)
, @SQL1 VARCHAR(8000)
, @SQL2 VARCHAR(8000)
--SELECT 'RETRUN 주석처리하세요.'
--RETURN
-- 변경 테이블 및 필드
/*
DECLARE @SQLInfo TABLE(
InsertSql varchar(4000)
);
*/
DECLARE C_TABL CURSOR LOCAL FOR
SELECT
TableName
FROM
@TableInfo
ORDER BY
TableName
OPEN C_TABL
WHILE 1 = 1
BEGIN
FETCH NEXT FROM C_TABL INTO
@TableName
--
IF @@FETCH_STATUS = -1 BREAK
--
-- 삭제 쿼리
--SET @SQL1 = 'DELETE FROM ' + @TableName
SET @SQL1 = 'TRUNCATE TABLE ' + @TableName
-- 삭제
EXEC(@SQL1)
SET @T_COLUMN_COLLER = ''
SET @T_START = 0
DECLARE C_TMPL CURSOR LOCAL FOR
SELECT
ColumnName
FROM
@ColumnInfo
WHERE
TableName = @TableName
OPEN C_TMPL
WHILE 1 = 1
BEGIN
FETCH NEXT FROM C_TMPL INTO
@T_ColumnName
--
IF @@FETCH_STATUS = -1 BREAK
--
IF @T_START = 0
BEGIN
SET @T_COLUMN_COLLER = @T_ColumnName
END
ELSE
BEGIN
SET @T_COLUMN_COLLER = @T_COLUMN_COLLER + ', ' + @T_ColumnName
END
SET @T_START = @T_START + 1
--
END
CLOSE C_TMPL
DEALLOCATE C_TMPL
-- 인서트쿼리
SET @SQL2 = ''
--SET IDENTITY_INSERT TABLE ON
IF (@TableName IN (
'GEMTB_ZBoard'
,'GEMTB_ZBoardattachments'
,'GEMTB_ZBoardcontent'
,'GEMTB_ZSMRqstInfo'
,'XXEAI_TRX_HEADER_II'
,'XXEAI_TRX_LINES_II'
,'XXEAI_TRX_SCHEDULES_II'
,'EAIB_GMS_0032_GEM'
,'EAIB_GMS_0033_GEM'
,'EAIB_GMS_0034_GEM'
,'EAIB_GMS_0036_GEM'
,'EAIB_GMS_0037_GEM'
,'EAIB_GMS_0038_GEM'
,'EAIB_GMS_0039_GEM'
,'EAIB_GMS_0054_GEM'
,'EAIB_GMS_0055_GEM'
,'EAIB_GMS_0056_GEM'
,'EAIB_GSI_0013_GEM'
,'EAIB_GSI_0014_GEM'
,'EAIB_GSI_0015_GEM'
,'EAIB_GSI_0016_GEM'
,'EAIB_GSI_0017_GEM'
,'EAIB_GSI_0018_GEM'
,'EAIB_GSI_0019_GEM'
,'EAIB_GSI_0024_GEM'
,'EAIB_GSI_0025_GEM'
))
BEGIN
SET @SQL2 = 'SET IDENTITY_INSERT dbo.' + @TableName + ' ON; '
END
SET @SQL2 = @SQL2 + 'INSERT INTO ' + @TableName + '(' + @T_COLUMN_COLLER + ')'
SET @SQL2 = @SQL2 + ' SELECT '+ @T_COLUMN_COLLER +' FROM ' + 'OPENDATASOURCE(''SQLOLEDB'',''SERVER=100.44.44.44;UID=user;PWD=pw'').GPM.dbo.' + @TableName
-- 인서트
EXEC(@SQL2)
print ' TABLE : '+@TableName
--SET IDENTITY_INSERT TABLE OFF
IF (@TableName IN (
'GEMTB_ZBoard'
,'GEMTB_ZBoardattachments'
,'GEMTB_ZBoardcontent'
,'GEMTB_ZSMRqstInfo'
,'XXEAI_TRX_HEADER_II'
,'XXEAI_TRX_LINES_II'
,'XXEAI_TRX_SCHEDULES_II'
,'EAIB_GMS_0032_GEM'
,'EAIB_GMS_0033_GEM'
,'EAIB_GMS_0034_GEM'
,'EAIB_GMS_0036_GEM'
,'EAIB_GMS_0037_GEM'
,'EAIB_GMS_0038_GEM'
,'EAIB_GMS_0039_GEM'
,'EAIB_GMS_0054_GEM'
,'EAIB_GMS_0055_GEM'
,'EAIB_GMS_0056_GEM'
,'EAIB_GSI_0013_GEM'
,'EAIB_GSI_0014_GEM'
,'EAIB_GSI_0015_GEM'
,'EAIB_GSI_0016_GEM'
,'EAIB_GSI_0017_GEM'
,'EAIB_GSI_0018_GEM'
,'EAIB_GSI_0019_GEM'
,'EAIB_GSI_0024_GEM'
,'EAIB_GSI_0025_GEM'
))
BEGIN
SET @SQL2 = 'SET IDENTITY_INSERT dbo.' + @TableName + ' OFF; '
EXEC(@SQL2)
END
--INSERT INTO @SQLInfo (InsertSql) VALUES (@SQL2)
END
CLOSE C_TABL
DEALLOCATE C_TABL
--SELECT * FROM @SQLInfo
GO
-----------------------------------------------------------------------------------------------------------------------
--종료 : 가동DB의 TABLE DATA를 개발DB로 이행 합니다.
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
--트리거를 ENABLE 시킵니다.
-----------------------------------------------------------------------------------------------------------------------
SELECT
'ENABLE TRIGGER '+a.NAME+' ON '+b.name+';'
FROM
sys.sysobjects a
inner join sys.sysobjects b
on a.parent_obj = b.id
WHERE
a.xtype = 'TR'
ORDER BY a.xtype, a.name;
ENABLE TRIGGER EAIT_INSERT_GSI_0013_GEM ON GEMTB_NPayment;
ENABLE TRIGGER EAIT_INSERT_GSI_0014_GEM ON GEMTB_AVendorMaster;
ENABLE TRIGGER EAIT_INSERT_GSI_0015_GEM ON GEMTB_BCtrtMaster;
ENABLE TRIGGER EAIT_INSERT_GSI_0017_GEM ON GEMTB_BProjectSumm;
ENABLE TRIGGER EAIT_INSERT_GSI_0018_GEM ON GEMTB_OEndSchdCostMgm;
ENABLE TRIGGER EAIT_INSERT_GSI_0019_GEM ON GEMTB_DPerformBudgetBasc;
ENABLE TRIGGER EAIT_INSERT_GSI_0024_GEM ON GEMTB_BContractUnitSumm;
ENABLE TRIGGER EAIT_UPDATE_GSI_0013_GEM ON GEMTB_NPayment;
ENABLE TRIGGER EAIT_UPDATE_GSI_0014_GEM ON GEMTB_AVendorMaster;
ENABLE TRIGGER EAIT_UPDATE_GSI_0015_GEM ON GEMTB_BCtrtMaster;
ENABLE TRIGGER EAIT_UPDATE_GSI_0016_GEM ON XXEAI_TRX_HEADER_II;
ENABLE TRIGGER EAIT_UPDATE_GSI_0017_GEM ON GEMTB_BProjectSumm;
ENABLE TRIGGER EAIT_UPDATE_GSI_0018_GEM ON GEMTB_OEndSchdCostMgm;
ENABLE TRIGGER EAIT_UPDATE_GSI_0019_GEM ON GEMTB_DPerformBudgetBasc;
ENABLE TRIGGER EAIT_UPDATE_GSI_0024_GEM ON GEMTB_BContractUnitSumm;
ENABLE TRIGGER EAIT_UPDATE_GSI_0025_GEM ON GEMTB_NPayment;
-----------------------------------------------------------------------------------------------------------------------
'프로그램 경험 > Database' 카테고리의 다른 글
| [SqlServer] OPENROWSET / OPENDATASOURCE (0) | 2013.07.19 |
|---|---|
| [SqlServer] 키 자동증가 테이블에 명시적으로 키값 입력 하기 (0) | 2013.07.04 |
| [SqlServer] sa 비번 변경 및 Error: 18456, 18470 (0) | 2013.02.27 |
| [SqlServer] 원격 DB 조회 하기(OpenRowSet) (0) | 2013.02.13 |
| [SqlServer] 시스템 테이블 (0) | 2013.01.15 |