테이블에서 데이터 뽑아내기 (INSERT 구문으로)

USE [SMS]

GO

/***************************************************************

프로그램명 : SP_TOOL_MakeTableDataScript

용      도 : 테이블 데이터 스크립트 생성

이 력 : 2011-06-21 (오현석) 생성

***************************************************************/

DECLARE @IN_TableName VARCHAR(128)

DECLARE @IN_SortColumn VARCHAR(128)

SET @IN_TableName = 'T_ObjectType'

SET @IN_SortColumn = 'TypeID'

-- ======================================================================

-- DEFINE VARIABLE

-- ======================================================================

DECLARE @Count INT

DECLARE @TableID INT

DECLARE @ColumnCount INT

DECLARE @SQLLeft VARCHAR (4000)

DECLARE @SQLRight VARCHAR (4000)

DECLARE @IsHasIdent INT

DECLARE @C_ColumnNum INT

DECLARE @C_ColumnName VARCHAR (30)

DECLARE @C_ColumnType INT

CREATE TABLE #ColumnInfo

(

num INT IDENTITY(1,1),

name VARCHAR(30),

usertype SMALLINT

)

-- ======================================================================

-- INITIALIZE

-- ======================================================================

SET NOCOUNT ON

-- ======================================================================

-- MAIN PROCESS

-- ======================================================================

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

-- # Get Table info

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

-- Get table id

SELECT @TableID = id

FROM sysobjects

WHERE type in ('U')

and name = @IN_TableName

-- Get data whether have identity column

SELECT @IsHasIdent = count(*)

FROM syscolumns

WHERE id = @TableID

and (status & 0x80) = 0x80

-- Get column info and insert to temporary table

INSERT INTO #ColumnInfo (name, usertype)

SELECT name, xtype

FROM syscolumns

WHERE id = @TableID

SELECT @ColumnCount = max(num)

FROM #columninfo

/*

SELECT @ColumnCount = min(num),

@ColumnCountMax = max(num)

FROM #columninfo

*/

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

-- # Make script #1

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

SELECT @SQLLeft = 'SELECT ''INSERT INTO ' + @IN_TableName

SELECT @SQLLeft = @SQLLeft + '('

DECLARE C1 CURSOR FOR

SELECT num, name, usertype

FROM #columninfo

ORDER BY num

OPEN C1

FETCH NEXT FROM C1 INTO @C_ColumnNum, @C_ColumnName, @C_ColumnType

WHILE @@FETCH_STATUS = 0

BEGIN

-- Make script

IF (@C_ColumnNum < @ColumnCount)

SELECT @SQLLeft = @SQLLeft + @C_ColumnName + ','

ELSE

SET @SQLLeft = @SQLLeft + @C_ColumnName + ')'

-- Get Nect Cursor

FETCH NEXT FROM C1 INTO @C_ColumnNum, @C_ColumnName, @C_ColumnType

END

CLOSE C1

DEALLOCATE C1

 

SELECT @SQLLeft = @SQLLeft + CHAR(13) + '    VALUES ('''

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

-- # Make script #2

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

SELECT @SQLRight = ''

DECLARE C2 CURSOR FOR

SELECT num, name, usertype

FROM #columninfo

ORDER BY num

OPEN C2

FETCH NEXT FROM C2 INTO @C_ColumnNum, @C_ColumnName, @C_ColumnType

WHILE @@FETCH_STATUS = 0

BEGIN

-- Make script

IF @C_ColumnType in (36, 167, 231) /* vachar(39, 167), nVARCHAR(47, 231) */

BEGIN

SELECT @SQLRight = @SQLRight + '+'

SELECT @SQLRight = @SQLRight + 'ISNULL(' + REPLICATE(CHAR(39), 4) + '+REPLACE(' + @C_ColumnName + ',' + REPLICATE( CHAR(39), 4 ) + ',' + REPLICATE( CHAR(39), 6) + ')+' + REPLICATE ( CHAR(39), 4 ) + ',''NULL'')'

END

ELSE IF @C_ColumnType = 35 /* TEXT fields cannot be RTRIM-ed and need quotes. CONVERT to VC 1000 to leave space for other fields */

BEGIN

SELECT @SQLRight = @SQLRight + '+'

SELECT @SQLRight = @SQLRight + 'ISNULL(' + REPLICATE(CHAR(39), 4) + '+REPLACE(CONVERT(VARCHAR(1000),' + @C_ColumnName + ')' + ',' + REPLICATE( CHAR(39), 4 ) + ',' +REPLICATE( CHAR(39), 6 ) + ')+' + REPLICATE( CHAR(39), 4 ) + ',''NULL'')'

END

ELSE IF @C_ColumnType in (58,61,111) /* datetime fields */

BEGIN

SELECT @SQLRight = @SQLRight + '+'

SELECT @SQLRight = @SQLRight + 'ISNULL(' + REPLICATE(CHAR(39), 4) + '+CONVERT(VARCHAR(20),' + @C_ColumnName + ', 120)+'+ REPLICATE( CHAR(39), 4 ) + ',''NULL'')'

END

ELSE   /* numeric types */

BEGIN

SELECT @SQLRight = @SQLRight + '+'

SELECT @SQLRight = @SQLRight + 'ISNULL(CONVERT(VARCHAR(99),' + @C_ColumnName+ '),''NULL'')'

END

IF ( @C_ColumnNum < @ColumnCount)

BEGIN

SELECT @SQLRight = @SQLRight + '+'','''

END

-- Get Nect Cursor

FETCH NEXT FROM C2 INTO @C_ColumnNum, @C_ColumnName, @C_ColumnType

END

CLOSE C2

DEALLOCATE C2

SELECT @SQLRight = @SQLRight + '+'')''' + ' FROM ' + @IN_TableName

SELECT @SQLRight = @SQLRight + ' ORDER BY ' + @IN_SortColumn

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

-- # Make script #3

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

IF @IsHasIdent > 0

BEGIN

SELECT 'SET IDENTITY_INSERT ' + @IN_TableName + ' ON'

END

EXEC ( @SQLLeft + @SQLRight )

-- ======================================================================

-- END PROCESS

-- ======================================================================

DROP TABLE #ColumnInfo

GO