본문 바로가기

프로그램 경험/Database

[SqlServer] 원격지 데이터 이관


-----------------------------------------------------------------------------------------------------------------------

--선행작업으로 가동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;

-----------------------------------------------------------------------------------------------------------------------