100만년전 ASP insert 쿼리로 날코딩으로 할때 만들었던...
긁어서 써보니 에러가 나네요...중간중간 게시물 등록되면서 불필요한 공백이 들어가서..
다운받아서 사용하기!
download : insertQuery.sql
DECLARE @tableName varchar(200)
DECLARE @COLUMN_NAME varchar(200) , @DATA_TYPE varchar(200) , @COLUMN_DEFAULT varchar(200)
DECLARE @SQL varchar(8000) , @INCOLSQL varchar(2000), @VALUESQL varchar(2000)
SET @tableName = 'tblQuotationDetailRate'
SET @INCOLSQL = ''
SET @VALUESQL = ''
SET @COLUMN_NAME = ''
SET @DATA_TYPE =''
SET @COLUMN_DEFAULT = ''
DECLARE tnames_cursor CURSOR
FOR
SELECT COLUMN_NAME ,DATA_TYPE , COLUMN_DEFAULT FROM information_schema.columns
WHERE table_name =@tableName
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor
INTO @COLUMN_NAME , @DATA_TYPE , @COLUMN_DEFAULT
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> - 2 )
BEGIN
IF @INCOLSQL = ''
BEGIN
SET @INCOLSQL = @COLUMN_NAME
SET @VALUESQL =
CASE
WHEN @DATA_TYPE ='nvarchar'
or @DATA_TYPE ='nchar'
or @DATA_TYPE ='ntext'
THEN 'N''" & ' + @COLUMN_NAME + ' & "'''
WHEN @DATA_TYPE ='varchar'
or @DATA_TYPE ='char'
or @DATA_TYPE ='varbinary'
THEN '''" & ' + @COLUMN_NAME + ' & "'''
WHEN @DATA_TYPE ='int'
or @DATA_TYPE ='bigint'
or @DATA_TYPE ='numeric'
or @DATA_TYPE ='smallint'
or @DATA_TYPE ='float'
or @DATA_TYPE ='real'
or @DATA_TYPE ='tinyint'
THEN '" & ' + @COLUMN_NAME + ' & "'
WHEN @DATA_TYPE ='smalldatetime'
or @DATA_TYPE ='datetime'
THEN ' dbo.fnTWGetDate() '
ELSE
' ''" & ' + @COLUMN_NAME + ' &" '''
END
END
ELSE
BEGIN
SET @INCOLSQL = @INCOLSQL + ',' + @COLUMN_NAME
SET @VALUESQL =
CASE
WHEN @DATA_TYPE ='nvarchar'
or @DATA_TYPE ='nchar'
or @DATA_TYPE ='ntext'
THEN @VALUESQL + ', N''" & ' + @COLUMN_NAME + ' & "'''
WHEN @DATA_TYPE ='varchar'
or @DATA_TYPE ='char'
or @DATA_TYPE ='varbinary'
THEN @VALUESQL + ', ''" & ' + @COLUMN_NAME + ' & "'''
WHEN @DATA_TYPE ='int'
or @DATA_TYPE ='bigint'
or @DATA_TYPE ='numeric'
or @DATA_TYPE ='smallint'
or @DATA_TYPE ='float'
or @DATA_TYPE ='real'
or @DATA_TYPE ='tinyint'
THEN @VALUESQL + '," & '+@COLUMN_NAME +' & " '
WHEN @DATA_TYPE ='smalldatetime'
or @DATA_TYPE ='datetime'
THEN @VALUESQL + ', dbo.fnTWGetDate() '
ELSE
@VALUESQL + ', ''' + @COLUMN_NAME + ''''
END
END
END
FETCH NEXT FROM tnames_cursor
INTO @COLUMN_NAME, @DATA_TYPE, @COLUMN_DEFAULT
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
IF @INCOLSQL = ''
BEGIN
print 'Not Avail Table Name'
END
ELSE
BEGIN
print 'INSERT INTO ' + @tableName
print ' (' + @INCOLSQL + ')'
print ' VALUES'
print '(' + @VALUESQL + ')'
END
INSERT INTO tblBkRemark
(bkNo,seq,writerDt,rewriterDt,status,opUid,question,answer)
VALUES
('" & bkNo & "'," & seq & " , dbo.fnTWGetDate() , dbo.fnTWGetDate() , '" & status & "', '" & opUid & "', N'" & question & "', N'" & answer & "')