[MSSQL] 동적 임시테이블 생성

Stored Procedure에서 종종 테이블 형태의 데이터를 임시로 저장해야 할 필요가 있습니다. MS SQL에서는 이를 위해 총 4가지 방법이 제공되고 있습니다. ( 아래의 표 출처 : points.tistory.com/16 : 정말 정리를 잘해놓으셨습니다. 추가적인 내용이 많으니 반드시 접속 하셔서 공부해보세요~)

비고Local Temporary TableGlobal Temporary TablePermanent TableTable Variable

비고	Local Temporary Table	Grobal Temporary Table	Permanent Table	Table Variable
문법	CREATE TABLE #TableName {   Id INT }	CREATE TABLE ##TableName {   Id INT }	CREATE TABLE tableName {   Id INT }	DECLARE @tableName TABLE {   Id INT }
	Table명은 116자 제한	Table명은 116자 제한	Table명은 128자 제한	Table명은 128자 제한
범위	현재 Procedure 또는 Session, 하위 Procedure, Session	모든 세션	모든 세션	해당 Scope (Procedure, Function 등)
삭제	1. DROP TABLE 2. SCOPE 또는 Session이 종료되는 시점에 자동삭제	1. DROP TABLE 2. 현재 Session이 종료되고 참조하는 작업이 모두 종료되었을 때 자동삭제	DROP TABLE	Scope를 벗어나면 자동삭제
외부 TRANSACTION, ROLLBACK의 영향	예	예	예	아니요
LOCKING	최소한의 잠금 (현재 사용자만 Table에 유일하게 접근할 수 있기 때문)	Permanent Table보다 약간 작은 잠금	예	아니요
LOGGING	최소한의 로깅	Permanent Table보다 약간작은 LOGGING	예	최소
크기	제한없음	제한없음	제한없음	제한없음
수정 시 재컴파일	예	예		최소
저장	tempdb	tempdb		tempdb

---

동적쿼리(adhoc) 실행 시 임시테이블(Local Temporary Table)은 생성되지 않습니다.

전역임시테이블(Global Temporary Table)을 사용해야 합니다. 단, 동시 사용의 경우 교착상태가 발생할 수 있으므로 Key 처리 해야 함.

※ 한글이 깨지는 경우가 있습니다. 그것도 테스트 해야 합니다.

BEGIN
	SET NOCOUNT ON
	SET ARITHABORT ON

	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @CNT SMALLINT

	SET @CNT = 7

	/* 임시 테이블 생성 */
	IF EXISTS (SELECT TOP 1 1 FROM tempdb..sysobjects  WHERE id=object_id('tempdb..##EXCL_TMP'))
	       DROP TABLE ##EXCL_TMP

	SET @SQL = 'CREATE TABLE ##EXCL_TMP ( A NVARCHAR(100)'

	SELECT @SQL = @SQL + ', B  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', C  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', D  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', E  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', F  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', G  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', H  NVARCHAR(100)'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', I  NVARCHAR(100)'      -- 동적대상SQL
    
	SELECT @SQL = @SQL + ')'

	EXEC (@SQL)

	/* 생성된 임시 테이블에 INSERT */
	SET @SQL = 'INSERT INTO ##EXCL_TMP ( A'
	
	SELECT @SQL = @SQL + ', B'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', C'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', D'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', E'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', F'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', G'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', H'      -- 동적대상SQL
	SELECT @SQL = @SQL + ', I'      -- 동적대상SQL
	
	SELECT @SQL = @SQL + ') VALUES ('

	SELECT @SQL = @SQL + '  ''TEST0'''
	SELECT @SQL = @SQL + N', N''테스트'''  -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''5000'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''1000'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''8000'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''1001'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''2001'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''3001'''      -- 동적대상SQL
	SELECT @SQL = @SQL + ', ''4001'''      -- 동적대상SQL

	SELECT @SQL = @SQL + ')'

	EXEC (@SQL)

	/* 임시 테이블 조회 */
	SELECT * FROM ##EXCL_TMP

	SET ARITHABORT OFF
	SET NOCOUNT OFF

END

DROP TABLE ##EXCL_TMP

결과

---

프로그램 라인을 줄이려면...

BEGIN
	SET NOCOUNT ON
	SET ARITHABORT ON

	DECLARE @SQL  NVARCHAR(MAX)
	DECLARE @I    SMALLINT
	DECLARE @CNT  SMALLINT
	DECLARE @NVAR NVARCHAR(100)

	SET @I = 1
	SET @CNT = 8

	/* 임시 테이블 생성 */
	IF EXISTS (SELECT TOP 1 1 FROM tempdb..sysobjects  WHERE id=object_id('tempdb..#EXCL_TMP'))
	       DROP TABLE #EXCL_TMP

	CREATE TABLE #EXCL_TMP ( CK NVARCHAR(100) )

	SET @SQL = N''

	WHILE (@I <= @CNT) BEGIN
		SET @SQL = 'ALTER TABLE #EXCL_TMP ADD C' + CAST(@I AS VARCHAR(10)) + ' NVARCHAR(100) '
		EXEC(@SQL)
		SET @I = @I + 1
	END

	/* 생성된 임시 테이블에 INSERT */
	SET @I = 1

	SET @SQL = 'INSERT INTO #EXCL_TMP ( CK'
	
	WHILE (@I <= @CNT) BEGIN
		SELECT @SQL = @SQL + ', C'+ CAST(@I AS VARCHAR(10))
		set @I = @I + 1
	END
	
	SELECT @SQL = @SQL + ') VALUES ('

	SELECT @SQL = @SQL + '  ''TEST0'''
	SELECT @SQL = @SQL + N', N''테스트'''
	SELECT @SQL = @SQL + ', ''5000'''
	SELECT @SQL = @SQL + ', ''BAR'''
	SELECT @SQL = @SQL + ', ''1'''
	SELECT @SQL = @SQL + ', ''1001'''
	SELECT @SQL = @SQL + ', ''2001'''
	SELECT @SQL = @SQL + ', ''3001'''
	SELECT @SQL = @SQL + ', ''4001'''

	SELECT @SQL = @SQL + ')'

	EXEC (@SQL)

	/* 임시 테이블 조회 */
	SELECT * FROM #EXCL_TMP

	SET ARITHABORT OFF
	SET NOCOUNT OFF

END

DROP TABLE #EXCL_TMP

결과