RE: Insertion in table – SQLServerCentral

ChrisM@Work

SSC Guru

Points: 186127

March 19, 2013 at 7:19 am

#1598411

Have a play with this;

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

), --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(),

DATEADD(MM,6,GETDATE()),

1,

'TRN' + CAST(b.BookNo AS VARCHAR(6))

FROM Booklets b

CROSS JOIN Pages p

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden