Insert Statements Creator / Generator Method 2

Insert Statements Creator / Generator Method 2


Create Procedure dbo.usp_GBulkInsert1
(
@pNumOfRows Int,
@pTableName VarChar(500),
@pValueList VarChar(Max),
@pColumnList VarChar(Max) = Null,
@pIdentityInsert Bit = 0,
@pDebug Bit = 0,
@pErrorFlag Char(1) Output,
@pErrorDesp VarChar(100) Output
)
As
Begin
Set NoCount On
/*
--To Execute

Declare @lNumOfRows Int,
@lTableName VarChar(500),
@lValueList VarChar(Max),
@lColumnList VarChar(Max),
@lIdentityInsert Bit,
@lDebug Bit,
@lErrorFlag Char(1),
@lErrorDesp VarChar(100)
Select @lTableName ='Customer',
@lValueList ='ZZ,Z', --'Z, YY' --'YY, YY, 7'
@lColumnList ='', --'CustomerID, ContactName' --'CustomerID, ContactName, UniverstalID'
@lIdentityInsert =0,
@lDebug = 0
Exec usp_GInsertStatement @lTableName, @lValueList, @lColumnList, @lIdentityInsert, @lDebug, @lErrorFlag Output, @lErrorDesp Output
Select @lDebug DebugFlag, @lErrorFlag ErrorFlag, @lErrorDesp ErrorDescription

*/

Declare @lValueList VarChar(Max),
@lPointer Int,
@lValue VarChar(Max)

--To Save Transaction with TableName
If @@TranCount > 0
Exec ('Save Transaction BulkInsert' + @pTableName)
Select @lPointer = 0
While @lPointer <> @pNumOfRows
Begin
If @lPointer = 0
Begin
--To Insert Data as provided
Exec usp_GInsertStatement @pTableName, @pValueList, @pColumnList, @pIdentityInsert, @pDebug, @pErrorFlag Output, @pErrorDesp Output
If @pErrorFlag = 'Y'
GoTo ErrorHandler
End
Else
Begin
--To Insert Bulk Data
Select @lValueList = lTrim(rTrim(@pValueList)) + ' ,', @pValueList = ''
While PatIndex('%,%', @lValueList) > 0
Begin
Select @lValue = lTrim(rTrim(SubString(@lValueList, 1, (PatIndex('%,%', @lValueList) -1))))
Select @lValueList = SubString(@lValueList, (PatIndex('%,%', @lValueList) +1), Len(@lValueList))
If IsNumeric(@lValue) = 1
Select @lValue = Convert(Numeric(16,2), @lValue) + 1
Else If IsDate(@lValue) = 1
Select @lValue = DateAdd(d,1, @lValue)
Else
Select @lValue = @lValue + Char(@lPointer + 64)

Select @pValueList = @pValueList + @lValue + ' ,'
End

Exec usp_GInsertStatement @pTableName, @pValueList, @pColumnList, @pIdentityInsert, @pDebug, @pErrorFlag Output, @pErrorDesp Output
If @pErrorFlag = 'Y'
GoTo ErrorHandler
End
Select @lPointer = @lPointer + 1
End

Select @pErrorFlag = 'N', @pErrorDesp = ''
Set NoCount Off
Return
ErrorHandler:
--To Rollback Transaction
If @@TranCount > 0
Exec ('Rollback Transaction BulkInsert' + @pTableName)
Set NoCount Off
End