-- Inline tally from Jeff Moden et al.DECLARE @LastBookNo INT = 23, @LastPageNo INT = 80, @BooksToInsert INT = 2, @PagesPerBook INT = 10;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Booklets AS (SELECT TOP (@BooksToInsert) BookNo = @LastBookNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E4), Pages AS (SELECT TOP (@PagesPerBook) N FROM E4)SELECT BookNo, PageNo = @LastPageNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM Booklets bCROSS JOIN Pages p

WHILE @count <= @quantityBEGIN WHILE @innercount < @leaf BEGIN SET @max = (select vouchervaluebased from gv_maxvouchervalue) SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue) INSERT INTO GV_Booklet VALUES ( @leaf, 100, 1, 'VB' + Replace( Str( @max+1, 7), ' ', '0') , @quantity, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') ) SET @innercount = @innercount + 1 UPDATE gv_maxvouchervalue SET vouchervaluebased = @max + 1 END SET @innercount = 1 SET @count = @count + 1END

DECLARE @LastBookNo INT = 23, @LastPageNo INT = 80, @BooksToInsert INT = 2, @PagesPerBook INT = 10 ;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Booklets AS (SELECT TOP (@BooksToInsert) BookNo = @LastBookNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E4), Pages AS (SELECT TOP (@PagesPerBook) N FROM E4)SELECT @PagesPerBook , 100, 1, 'VB' + CAST(@LastPageNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(6)), @BooksToInsert, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + CAST(b.BookNo AS VARCHAR(6))FROM Booklets bCROSS JOIN Pages p

DECLARE @LastBookNo INT = 23, @LastPageNo INT = 80, @BooksToInsert INT = 2, @PagesPerBook INT = 10 ;WITH-- Make an inline tally table (CTE) E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max-- Use the tally CTE to construct a booklets CTE -- containing one row for each booklet required, in this case, 2 booklets.-- Adding the row number to the last book number (obtained elsewhere) yields-- the new book numbers Booklets AS (SELECT TOP (@BooksToInsert) BookNo = @LastBookNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E4), -- use the tally CTE to construct a Pages CTE containing one row per page required-- in this case, 10 pages per book Pages AS (SELECT TOP (@PagesPerBook) N FROM E4)-- cross join the Booklets CTE (2 rows) and the Pages CTE (10 rows) to generate 20 rows.-- We've already calculated the two new BookNo values in the Booklets CTE-- In this query we perform the same type of operation with the page number-- ROW_NUMBER() numbers the rows 1 to 20, added to @LastPageNo gives the new page number SELECT @PagesPerBook , 100, 1, 'VB' + CAST(@LastPageNo + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(6)), @BooksToInsert, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + CAST(b.BookNo AS VARCHAR(6))FROM Booklets bCROSS JOIN Pages p