MS-SQL에서 특정테이블의 데이터값으로 INSERT 구문 생성하기

특정데이터의 값을 다른 SQL로 옮기는 일이 자주 발생하였다.
처음에는 일일이 데이터를 옮겼는데 여러번 발생을 하다보니 한번에 옮길 방법이 필요했다.

테이블 전체 데이터를 옮길려면 태스크 > 데이터 내보내기 방법을 사용하여 옮길 수 있지만
전체가 아닌 특정 데이터들만 옮길때는 불편한 점이 발생하여,
이번 기회에 INSERT문을 만들어주는 쿼리를 만들어야 겠다 생각이 들어 하나 만들게 되었다.

저와 비슷하게 데이터를 옮길 일이 발생하신 분들은 유용하게 사용하시면 좋겠습니다.

 

DECLARE @TableName NVARCHAR(100) = 'your table name'; -- 테이블 이름
DECLARE @Columns NVARCHAR(MAX); -- 동적 칼럼 목록
DECLARE @Values NVARCHAR(MAX); -- 동적 select convert
DECLARE @SQL NVARCHAR(MAX);     -- 최종 SQL 쿼리 저장

-- 1. 칼럼 이름 가져오기
SELECT @Columns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

SELECT @Values = STRING_AGG(
			   CASE 
				   -- 문자열 처리
				   WHEN QUOTENAME(DATA_TYPE) IN ('[nvarchar]', '[varchar]', '[char]', '[nchar]', '[text]') 
					   THEN 'ISNULL('''''''' + CAST(' + QUOTENAME(COLUMN_NAME) + ' AS NVARCHAR(MAX)) + '''''''', ''NULL'') ' 
				   -- 숫자 처리
				   WHEN QUOTENAME(DATA_TYPE) IN ('[int]','[bigint]', '[smallint]', '[tinyint]', '[decimal]', '[float]') 
					   THEN 'ISNULL('''''''' + CAST(' + QUOTENAME(COLUMN_NAME) + ' AS NVARCHAR(MAX)) + '''''''' , ''NULL'')'
				   -- 날짜 처리
				   WHEN QUOTENAME( DATA_TYPE) IN ('[datetime]', '[datetime2]', '[date]', '[smalldatetime]') 
					   THEN 'ISNULL('''''''' + FORMAT(' + QUOTENAME(COLUMN_NAME) + ', ''yyyy-MM-dd HH:mm:ss'') + '''''''' , ''NULL'')'
				   -- NULL 처리
				   ELSE '''NULL'''
			   END, ' + '', '' + '
			) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

PRINT @Values

-- 2. INSERT 문 생성
SET @SQL = '
SELECT ''INSERT INTO ' + @TableName + ' (' + @Columns + ') 
		VALUES ('' + ' + @Values + ' + '' ) ''
FROM ' + @TableName;
--조건절 추가부분

-- 3. SQL 출력 
PRINT @SQL;

위 코드 마지막 PRINT 되어 출력된 SQL문을 데이터를 입력해야 할 DB에서 돌리면 TABLE INSERT가 됩니다.

만일 테이블 명이 다르다면 2번항목의 INSERT INTO 의 테이블 이름을 다르게 지정해 주어야 합니다.
저는 동일 테이블명이라 선언한 테이블명 변수 그대로 넣었습니다.