dbo.QBM_PBufferT_ProcessInsert

1     create   procedure QBM_PBufferT_ProcessInsert ( @ModuleName varchar(3) , @TableName varchar(30)  ) as begin declare @cmd nvarchar(max) declare
2 @insertlist nvarchar(max) declare @SelectList nvarchar(max) declare @JoinList nvarchar(max) declare @Vortest nvarchar(max) declare @number int = 0 declare
3 @columnname varchar(30) declare @MissingColumnname varchar(30) declare @NumberTableSynonym nvarchar(64) declare @ColumnDatatype nvarchar(64) declare @ColumnDataMaxLen
4 int declare @countColumnsInRow int declare @RowsPerShot int declare @lauf int declare @XUser nvarchar(64) = object_name(@@procid) declare @DebugLevel 
5char(1) = 'W' declare @DebugSwitch int = 0 declare @columnPattern nvarchar(max) declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare
6 @ErrorState int declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY select @countColumnsInRow
7 = count(distinct columnname) from #QBMBufferTransferOneTable  if @countColumnsInRow = 0 begin select @cmd = '#LDS#Table {0} not inserted, no Column content delivered|'
8 + @TableName + '|' exec QBM_PJournal @cmd, @@procid, 'D', @DebugLevel goto EndLabel end if not exists (select top 1 1 from QBM_VStartupTables su where
9 su.TableName = @TableName ) begin select @MissingColumnname = null select top 1 @MissingColumnname = x.ColumnName from ( select distinct bu.ColumnName
10 from #QBMBufferTransferOneTable bu  ) as x left outer join ( select columnname from DialogColumn c with (readpast) join DialogTable t with (readpast) 
11on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName ) as y on x.ColumnName = y.ColumnName where y.ColumnName is null if @MissingColumnname
12 > ' ' begin select @cmd = '#LDS#Table {0} not inserted because of missing ColumnDefinition {1}, processing module {2}|' + @TableName + '|' + @MissingColumnname
13 + '|' + @Modulename + '|' exec QBM_PJournal @cmd, @@procid, 'W', @DebugLevel goto EndLabel end end if @DebugSwitch > 0 begin print '@countColumnsInRow'
14 + str(@countColumnsInRow) end if @countColumnsInRow < 100  begin select @RowsPerShot = 400000 / @countColumnsInRow end else begin select @RowsPerShot 
15= 4000 end  select @columnPattern = N'
16		join ( select @ContentClause@ as @columnname@, bux.ObjectkeyOfRow as XObjectKey
17					from #QBMBufferTransferOneTable bux 
18					where bux.columnname = ''@columnname@''
19				) as @number@ on v000.XObjectKey = @number@.XObjectKey'
20 select @Vortest = N'
21declare @missingList QBM_YParameterList -- t a b l e (ObjectkeyOfRow varchar(138)  -->Parameter1
22
23inser'+'t into @missingList(Parameter1 /*ObjectkeyOfRow*/)
24select top '
25 + STR(@RowsPerShot) + ' y.ObjectKeyOfRow
26 from (
27		select ot.ObjectKeyOfRow -- , COUNT(*) as CountItems
28			from #QBMBufferTransferOneTable ot 
29			group by ot.ObjectKeyOfRow
30			having COUNT(*) =  '
31 + str(@countColumnsInRow) + '
32		) as y	
33		where Not exists (select top 1 1
34						 from ' + @TableName + ' x
35						 where x.XObjectKey = y.ObjectkeyOfRow
36						)
37
38'
39 select @insertlist = @Vortest + N'
40 if @@rowcount = 0
41  begin
42	goto EndLabel
43  end	
44  					
45Inser'+'t into ' + @TableName + ' (XObjectKey' select
46 @SelectList = N'
47	select v000.XObjectKey ' select @JoinList = N'
48from 
49 (
50	-- bestimmen aller einzufügenden Sätze
51	select Parameter1 /*ObjectkeyOfRow*/ as XObjectKey 
52		from @missingList
53	) as v000'
54   if @TableName in (select TableName from QBM_VStartupTables)  begin insert into @ElementBuffer(Ident1) select distinct bu.columnname from QBM_VSchemaColumns
55 cc join #QBMBufferTransferOneTable bu on cc.COLUMN_NAME = bu.columnname collate database_default where cc.TABLE_NAME = @TableName  and ( bu.HasContentFull
56 = 0 and isnull(bu.ContentShort, '') > ' ' or bu.HasContentFull = 1 and isnull(bu.ContentFull, '') > ' ' ) order by 1 end else begin insert into @ElementBuffer
57(Ident1) select distinct bu.columnname from dialogColumn cc with (readpast) join #QBMBufferTransferOneTable bu on cc.COLUMNNAME = bu.columnname join DialogTable
58 t with (readpast) on cc.UID_DialogTable = t.UID_DialogTable where t.TableName = @TableName  and ( bu.HasContentFull = 0 and isnull(bu.ContentShort, ''
59) > ' ' or bu.HasContentFull = 1 and isnull(bu.ContentFull, '') > ' ' ) order by 1 end select @ElementCount = count(*) from @ElementBuffer select @ElementIndex
60 = 1 while @ElementIndex <= @ElementCount begin select top 1 @ColumnName = bu.Ident1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select
61 @number += 1 select @NumberTableSynonym = 'v' + right('00' + ltrim(str(@number)), 3) if @DebugSwitch > 0 begin print 'Spalte : ' + @columnname end select
62 @ColumnDatatype = dbo.QBM_FGIColumnDataType(@TableName , @columnname) select @ColumnDataMaxLen = dbo.QBM_FGIColumnDataLen(@TableName , @columnname) select
63 @insertlist = @insertlist + ', ' + @columnname select @SelectList = @SelectList + N'	
64			, ' + dbo.QBM_FSQIsNullClauseSet(@TableName , @columnname, @NumberTableSynonym
65) select @JoinList = @JoinList + replace(REPLACE(REPLACE(@columnPattern , '@ContentClause@', dbo.QBM_FSQCVBufferToColumn(@TableName , @columnname, N'bux'
66, 0)) , '@columnname@', @columnname) , '@number@', @NumberTableSynonym) select @ElementIndex += 1 end  if dbo.QBM_FGIColumnExists(@TableName , 'XUserInserted'
67) = 1 begin select @insertlist = @insertlist + ', XuserInserted, XUserUpdated, XDateInserted, XDateupdated' select @SelectList = @SelectList + ', ''' +
68 @xuser + ''', ''' + @xuser + ''', getutcdate(), getutcdate()' end   if dbo.QBM_FGIColumnExists(@TableName , 'XIsInEffect') = 1  and not exists (select
69 top 1 1 from #QBMBufferTransferOneTable bu where bu.ColumnName = 'XIsInEffect' ) begin select @insertlist = @insertlist + ', XIsInEffect' select @SelectList
70 = @SelectList + ', 1' end select @insertlist = @insertlist + ')' select @cmd = @insertlist + @SelectList + @JoinList + N'
71			EndLabel:
72			' if @DebugSwitch
73 > 0 begin print substring(@cmd, 1, 4000) print substring(@cmd, 4001, 4000) print substring(@cmd, 8001, 4000) print substring(@cmd, 12001, 4000) print 
74substring(@cmd, 16001, 4000) print substring(@cmd, 20001, 4000) end exec sp_executesql @Vortest select @lauf = @@ROWCOUNT  while @lauf > 0 begin begin 
75try exec sp_executesql @cmd select @lauf = @@ROWCOUNT end try begin catch select @lauf = 0 exec QBM_PSessionErrorAdd default, @cmd RAISERROR ('', 18, 1
76) WITH NOWAIT end catch end    END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() 
77RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 
78