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) )
====================================================================================
