MSSQL : 동적 데이터 복사 Stored Procedure 스크립트

 

 

INFORMATION_SCHEMA.COLUMNS와 STRING_AGG 함수를 활용하면, 테이블의 컬럼이 추가되거나 변경될 때마다 일일이 쿼리를 수정할 필요 없이 동적으로 쿼리를 생성하고 실행하는 강력한 Stored Procedure(SP)를 만들 수 있습니다.

 

목적(문자열 추출 또는 실제 실행)에 맞게, 테이블명과 타겟/소스 데이터베이스명을 파라미터로 받아 유연하게 동작하는 SP 스크립트입니다.

 

---

🛠️ 동적 데이터 복사 Stored Procedure 스크립트

 

이 SP는 @IsExecute 파라미터 값에 따라 단순히 쿼리를 텍스트로 출력(추출)할 수도 있고, 즉시 실행할 수도 있도록 설계되었습니다.

 

CREATE OR ALTER PROCEDURE usp_CopyTableData_Dynamic
    @SourceDB NVARCHAR(128),       -- 데이터를 가져올 Source DB명 (예: DB_INTRAWorkingHour)
    @TargetDB NVARCHAR(128),       -- 데이터를 넣을 Target DB명 (예: NpmUnivEsEW)
    @TableName NVARCHAR(128),      -- 대상 테이블명 (예: UNIV_WORK_ManagerConfig)
    @IsExecute BIT = 0             -- 0: 쿼리 문자열만 추출(PRINT 및 SELECT), 1: 쿼리 실제 실행
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @ColumnQuery NVARCHAR(MAX);
    DECLARE @IdentityCheckQuery NVARCHAR(MAX);
    DECLARE @HasIdentity BIT = 0;

    BEGIN TRY
        -- 1. 컬럼명 추출
        SET @ColumnQuery = N'
            SELECT @Cols = STRING_AGG(QUOTENAME(COLUMN_NAME), '', '') 
            FROM ' + QUOTENAME(@TargetDB) + N'.INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = @Tbl';

        EXEC sp_executesql 
            @ColumnQuery, 
            N'@Tbl NVARCHAR(128), @Cols NVARCHAR(MAX) OUTPUT', 
            @TableName, 
            @ColumnList OUTPUT;

        IF @ColumnList IS NULL
        BEGIN
            PRINT 'Error: [' + @TargetDB + '] 데이터베이스에 [' + @TableName + '] 테이블이 존재하지 않거나 컬럼이 없습니다.';
            RETURN;
        END

        -- 2. 대상 테이블에 IDENTITY 컬럼이 존재하는지 확인
        SET @IdentityCheckQuery = N'
            SELECT @HasId = CASE WHEN EXISTS (
                SELECT 1 
                FROM ' + QUOTENAME(@TargetDB) + N'.sys.identity_columns 
                WHERE object_id = OBJECT_ID(''' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TableName) + N''')
            ) THEN 1 ELSE 0 END';
        
        EXEC sp_executesql
            @IdentityCheckQuery,
            N'@HasId BIT OUTPUT',
            @HasIdentity OUTPUT;

        -- 3. INSERT 쿼리 동적 생성 (IDENTITY 존재 여부에 따라 분기)
        IF @HasIdentity = 1
        BEGIN
            SET @SQL = @SQL + N'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TableName) + N' ON;' + CHAR(13) + CHAR(10);
        END

        SET @SQL = @SQL + N'INSERT INTO ' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TableName) + N' (' + @ColumnList + N')' + CHAR(13) + CHAR(10) +
                   N'SELECT ' + @ColumnList + CHAR(13) + CHAR(10) +
                   N'FROM ' + QUOTENAME(@SourceDB) + N'.dbo.' + QUOTENAME(@TableName) + N';' + CHAR(13) + CHAR(10);

        IF @HasIdentity = 1
        BEGIN
            SET @SQL = @SQL + N'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TableName) + N' OFF;';
        END

        -- 4. 실행 또는 문자열 추출
        IF @IsExecute = 1
        BEGIN
            PRINT 'Executing Script for Table: ' + @TableName;
            EXEC sp_executesql @SQL;
            PRINT 'Execute Completed Successfully.';
        END
        ELSE
        BEGIN
            PRINT '--- [Generated Script] ---';
            PRINT @SQL;
            SELECT @SQL AS GeneratedSQL;
        END
    END TRY
    BEGIN CATCH
        -- 에러 발생 시 처리
        PRINT 'Error occurred during execution.';
        PRINT 'Error Message: ' + ERROR_MESSAGE();
    END CATCH
END
GO

 

 

---

💡 SP 실행 및 활용 방법

SP를 생성하신 후, 아래와 같이 상황에 맞게 파라미터를 변경하여 호출하시면 됩니다.

 

1. 쿼리 문자열만 추출 (테스트 및 확인용)

 

안전하게 쿼리가 어떻게 생성되는지 눈으로 확인하거나 복사하고 싶을 때 사용합니다. (@IsExecute = 0)

EXEC usp_CopyTableData_Dynamic 
    @SourceDB = 'DB_INTRAWorkingHour', 
    @TargetDB = 'NpmUnivEsEW', 
    @TableName = 'UNIV_WORK_ManagerConfig', 
    @IsExecute = 0;

 

 

 

2. 쿼리 즉시 실행 (실제 데이터 삽입)

 

생성된 쿼리를 확인했고, 바로 데이터를 밀어 넣고 싶을 때 사용합니다. (@IsExecute = 1)

EXEC usp_CopyTableData_Dynamic 
    @SourceDB = 'DB_INTRAWorkingHour', 
    @TargetDB = 'NpmUnivEsEW', 
    @TableName = 'UNIV_WORK_ManagerConfig', 
    @IsExecute = 1;

 

 

---

참고로, 테이블 구조를 동적으로 다룰 때는 IDENTITY 속성 유무를 체크하는 것이 필수적입니다. 그렇지 않으면 자동 증가 컬럼이 없는 테이블에서 쿼리가 실패하게 됩니다.

 

해당 테이블에 IDENTITY 컬럼이 존재하는지 시스템 테이블(sys.identity_columns)을 통해 검사하고, 결과에 따라 SET IDENTITY_INSERT ON/OFF 구문을 유동적으로 추가하도록 되어 있습니다.