CREATE procedure [dbo].[sp_TablesSnapshot] @DataPart varchar(30) as DECLARE @FromShort char(3) If @DataPart = 'Test1' set @FromShort = 'T1' else If @DataPart = 'Test2' set @FromShort = 'T2' else If @DataPart = 'Test3' set @FromShort = 'T3' declare @PivotTableX TABLE ( ID Int, RECORDID VARCHAR(30), FROMTABLE VARCHAR(50), DESCRIPTION NCHAR(100), AMOUNT NVARCHAR(4000), Processed NCHAR(1) ) declare @RowCount Int Set @RowCount = (Select Count(ForeignKey) from dbo.vUnionTestTables) Declare @I INT Set @I = 1 WHILE (@I <= @RowCount) BEGIN DECLARE @PrimaryKey INT, @ForeignKey INT, @FromTable VARCHAR(3), @Description VARCHAR(100), @Amount NVARCHAR(4000) SELECT @PrimaryKey = PrimaryKey , @ForeignKey = ForeignKey , @FromTable = Case FromTable WHEN 'Test1' Then 'T1' WHEN 'Test2' Then 'T2' WHEN 'Test3' Then 'T3' END , @Description = description , @Amount = Amount FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ForeignKey) AS [RowNo] FROM dbo.vUnionTestTables) t1 WHERE t1.RowNo = @I And FromTable = @DataPart Insert into @PivotTableX (ID, RECORDID, FROMTABLE, DESCRIPTION, AMOUNT, Processed) values (@PrimaryKey, @ForeignKey, @FromTable, @Description, @Amount, 'F') Set @I = @I + 1 END declare @PivotTable TABLE ( ID Int, RECORDID VARCHAR(30), FROMTABLE VARCHAR(50), DESCRIPTION NCHAR(100), AMOUNT NVARCHAR(4000), Processed NCHAR(1) ) insert into @PivotTable (ID,RECORDID,FROMTABLE,DESCRIPTION,AMOUNT,PROCESSED) select distinct * from @PivotTableX where ID is not null Declare @MaxCount int declare @CountTable table ( RecordID int, FromTable nchar(3), idCount int ) insert into @CountTable (RecordID,FromTable,idCount) select RECORDID, FromTable, COUNT(ID) from @PivotTable GROUP by RECORDID, FromTable order by RECORDID Set @MaxCount = (select MAX(idcount) from @CountTable where FromTable = @FromShort and RecordID is not null) Declare @ColumnsErString varchar(5000) Declare @ColumnsMeString varchar(5000) Declare @ColumnsQmrString varchar(5000) Declare @BuildColumnsString varchar(5000) Set @BuildColumnsString = 'CREATE table ' + rtrim(@FromShort) + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey varchar(30), ' Set @I = 1 WHILE (@I <= @MaxCount) Begin If @I <> 1 set @BuildColumnsString = @BuildColumnsString + ', ' Set @BuildColumnsString = @BuildColumnsString + rtrim(@FromShort) + 'Desc' + ltrim(str(@I)) + ' nchar(100), ' + rtrim(@FromShort) + 'Amt' + ltrim(str(@I)) + ' nvarchar(4000)' Set @I = @I + 1 End Set @BuildColumnsString = @BuildColumnsString + ')' Exec(@BuildColumnsString) Declare @TableIDStep varchar(30), @RecIDStep varchar(30), @RecID varchar(30) Declare @CurrentFrom varchar(3), @CurrentDesc nchar(100), @CurrentAmt nvarchar(4000) Declare @InsertStr varchar(5000), @InsertStep int, @CurrentStep varchar(30)
Select @RecIDStep = min(ID) from @PivotTable where FROMTABLE = @FromShort Set @TableIDStep = 1 Set @InsertStep = 1 while @TableIDStep <= @MaxCount begin while @RecIDStep is not null Begin select @RecID = RECORDID, @CurrentFrom = Fromtable, @CurrentDesc = replace(DESCRIPTION,char(39),char(39)+char(39)), @CurrentAmt = AMOUNT from @PivotTable where FromTable = @FromShort and ID = @RecIDStep If @CurrentStep <> @RecID Set @InsertStep = 1 Set @CurrentStep = @RecID Declare @CheckInsertStr nvarchar(100) Declare @CheckInsertParam nvarchar(100) Declare @ReturnCount int select @CheckInsertStr = 'select @ReturnCount = count (*) from ' + ltrim(rtrim(@FromShort)) + 'Temp where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID select @CheckInsertParam = '@ReturnCount int OUTPUT' exec sp_ExecuteSql @CheckInsertStr, @CheckInsertParam, @ReturnCount output If @ReturnCount = 0 Set @InsertStr = 'insert into ' + @CurrentFrom + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey,' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ') values (' + CHAR(39) + @RecID + CHAR(39) + ',' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + char(39) + ltrim(rtrim(isnull(@CurrentAmt, 0.00))) + CHAR(39) + ')' Else Set @InsertStr = 'update ' + @CurrentFrom + 'Temp set ' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentAmt)) + CHAR(39) + ' where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID exec(@InsertStr) update @PivotTable set Processed = 'T' where ID = @RecIDStep Select @RecIDStep = MIN(ID) from @PivotTable where FROMTABLE = @CurrentFrom and ID <> @RecIDStep and Processed <> 'T' Set @InsertStep = @InsertStep + 1 End Set @TableIDStep = @TableIDStep + 1 End
|
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!