본문으로 바로가기

insert into 테이블 구문을 만들때.. 컬럼이 많으면 상당히 손가락이 아파온다.
그러므로 그 아픈 insert into구문을 자동생성하게끔 만든 프로시저이다.

잘 만든것은 아니지만 유용하게 쓰시길...

/*
==========================================================================================
Insert Into 구문 만들어주는 프로시저
==========================================================================================*/
Create Procedure SP_Insert
(
 @objName nvarchar(100)
)
as
/*
___________________________________________________________________
Name:  'Insert into' SPROC Builder
Version: 1
Date:  2006/02/20
Author:  Eun-Sung Kim( Silvercastle )
Description:
insert into 테이블 구문을 만들때.. 컬럼이 많으면 상당히 손가락이 아파온다.
그러므로 그 아픈 insert into구문을 자동생성하게끔 만든 프로시저이다.
잘 만든것은 아니지만 유용하게 쓰시길 바라며.. 
 
*/
SET NOCOUNT ON
SELECT distinct
 dbo.syscolumns.name AS ColName,
 dbo.syscolumns.colorder AS ColOrder,
 dbo.syscolumns.length AS ColLen,
 dbo.systypes.xtype
INTO #t_obj
FROM        
 dbo.syscolumns INNER JOIN
 dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
 dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE    
 (dbo.sysobjects.name = @objName)
 AND
 (dbo.systypes.status <> 1)

DECLARE Table_Cursor CURSOR FOR( SELECT * FROM #t_obj )
OPEN Table_Cursor

 Declare @col_name nvarchar(100)
 Declare @col_Order int
 Declare @col_len int
 Declare @col_xtype int
 Declare @Len int
 Declare @type_name varchar(100)

FETCH NEXT FROM Table_Cursor
INTO @col_name, @col_Order, @col_len, @col_xtype

Declare @SelectSQL varchar(3000)
Set @SelectSQL = 'insert into ' + @objName + '( ' + @col_name + ', '

WHILE @@FETCH_STATUS = 0
BEGIN

   FETCH NEXT FROM Table_Cursor
 INTO @col_name, @col_Order, @col_len, @col_xtype

 Set @SelectSQL = @SelectSQL + @col_name + ', '

END
 
 Set @Len = len(@SelectSQL) - len(@col_name) - 2

 Set @SelectSql = Stuff(@SelectSQL, @Len,  len(@col_name) + 3, '')

 Set  @SelectSQL = @SelectSQL + ') '

 print @SelectSQL

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

DECLARE Table_Cursor CURSOR FOR( SELECT * FROM #t_obj )
OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor
INTO @col_name, @col_Order, @col_len, @col_xtype

Set @SelectSQL = 'values( ' +

CASE @col_xtype
    WHEN 34 THEN 'Image'
    WHEN 35 THEN 'Text'
    WHEN 48 THEN 'TinyInt'
    WHEN 52 THEN 'SmallInt'
    WHEN 56 THEN 'Int'
    WHEN 58 THEN 'SmallDateTime'
    WHEN 59 THEN 'Real'
    WHEN 60 THEN 'Money'
    WHEN 61 THEN 'DateTime'
    WHEN 62 THEN 'Float'
    WHEN 99 THEN 'NText'
    WHEN 104 THEN 'Bit'
    WHEN 106 THEN 'Decimal'
    WHEN 122 THEN 'SmallMoney'
    WHEN 127 THEN 'BigInt'
    WHEN 165 THEN 'VarBinary'
    WHEN 167 THEN 'VarChar'
    WHEN 173 THEN 'Binary'
    WHEN 175 THEN 'Char'
    WHEN 231 THEN 'NVarChar'
    WHEN 239 THEN 'NChar'
    ELSE '!MISSING'
end

+ '(' + Convert(varchar(10),@col_len) + ')' + ', '


WHILE @@FETCH_STATUS = 0
BEGIN

   FETCH NEXT FROM Table_Cursor
 INTO @col_name, @col_Order, @col_len, @col_xtype

 Set @type_name =

CASE @col_xtype
    WHEN 34 THEN 'Image'
    WHEN 35 THEN 'Text'
    WHEN 48 THEN 'TinyInt'
    WHEN 52 THEN 'SmallInt'
    WHEN 56 THEN 'Int'
    WHEN 58 THEN 'SmallDateTime'
    WHEN 59 THEN 'Real'
    WHEN 60 THEN 'Money'
    WHEN 61 THEN 'DateTime'
    WHEN 62 THEN 'Float'
    WHEN 99 THEN 'NText'
    WHEN 104 THEN 'Bit'
    WHEN 106 THEN 'Decimal'
    WHEN 122 THEN 'SmallMoney'
    WHEN 127 THEN 'BigInt'
    WHEN 165 THEN 'VarBinary'
    WHEN 167 THEN 'VarChar'
    WHEN 173 THEN 'Binary'
    WHEN 175 THEN 'Char'
    WHEN 231 THEN 'NVarChar'
    WHEN 239 THEN 'NChar'
    ELSE '!MISSING'
end
 
 Set @SelectSQL = @SelectSQL + @type_name + '(' + Convert(varchar(10),@col_len) + ')'  + ', '

END

 Set @Len = len(@SelectSQL) - len(@type_name) - 2 - len(@col_len) -2

 Set @SelectSql = Stuff(@SelectSQL, @Len,  len(@type_name) + 3 + len(@col_len) + 2, '')

 Set  @SelectSQL = @SelectSQL + ') '

 print @SelectSQL

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

DROP TABLE #t_obj
SET NOCOUNT OFF





실행방법

====================================================================================

Exec SP_insert titles

====================================================================================


출력화면

====================================================================================

insert into titles( title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate )

values( VarChar(6), VarChar(80), Char(12), Char(4), Money(8), Money(8), Int(4), Int(4), VarChar(200), DateTime(8) )

====================================================================================