SQL Insert iterative number and Id of select Table 1 into table2-CodePudding

I have a numeric sequence of items that I need to insert into an entire table [Table 2].

Table 2 is made of items related to Table 1.

I need to add a set number of elements (A SET of ITEMS) into Table 2 , each set for each ID of Table 1. ID 1 in Table 1 will have roughly 100 elements referenced in Table 2 items 1-99, 100-199 would be associated with ID 2.

That might not be as clear as the pseudo code below. That is what I am trying to achieve.

Pseudo Code is

For Each Record in Select All Records from Table 1 ***(this is where I need help to know how to do)***

SET @cnt=1;
While (@cnt <100)
Insert Into Table 2, PK=@cnt, Name_@Cnt, Record.Id 
SET @cnt =@cnt 1;
End

Maybe there is an easier way - by creating a temp table and insert on Innerjoin ?? I do not know I am rustier than I would like to be..

CodePudding user response：

On the latest version of SQL Server, this is simply a case of using GENERATE_SERIES:

INSERT INTO dbo.Table2 (<Column List>)
SELECT <T1 Column List>,
       GS.Value
FROM dbo.Table1 T1
     CROSS APPLY GENERATE_SERIES(1,100,1);

On older versions, then you'll want to use a Tally. Ideally you'll want to create a function, but you can do this inline:

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows
INSERT INTO dbo.Table2 (<Column List>)
SELECT <T1 Column List>,
       T.I
FROM dbo.Table1 T1
     CROSS JOIN Tally T;

If you wanted to create a function, then the one I use is the following:

CREATE FUNCTION fn.Tally (@End bigint, @StartAtOne bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @StartAtOne = 0
        UNION ALL
        SELECT TOP (@End)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
    SELECT I
    FROM Tally;

Then you could use that in your INSERT in a similar way to GENERATE_SERIES:

INSERT INTO dbo.Table2 (<Column List>)
SELECT <T1 Column List>,
       T.I
FROM dbo.Table1 T1
     CROSS APPLY fn.Tally(100,1);