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 & "')