qwert231 said:
Dr Herbie said:
*snip*

Thanks Herbie... I think that's heading in the right direction... but...

 

I created the NumbersTable function... then I call it with a test:

SELECT

* FROM NumbersTable(12,1512,1)

 

And I get this:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

You could always create a stored procedure as you suggested but with the cursor.  The SQL is just an example; you would need to turn it in to an sp.  I'm not a fan of a table holding a 1000 numbers as what happens when you need 1001 records?

 

-- these would sp parameters
DECLARE @staticField2 nvarchar(50)
DECLARE @staticField3 nvarchar(50)
DECLARE @startNumber int
DECLARE @endNumber int

 

/* Debug code to set the values of the parameters that would be passed in to the sp */
SET @staticField2 = 'SOMETHING1'
SET @staticField3 = 'SOMETHING2'
SET @startNumber = 317202
SET @endNumber = 318202

 

/* Now for the code itself */
DECLARE @i int
SET @i = @startNumber

 

-- this still does lots of inserts
WHILE @i <= @endNumber
BEGIN
    -- Do insert here; something like
    INSERT INTO TargetTable (Field1, Field2, Field3)
    VALUES (@i, @staticField2, @staticField3)
    SET @i = @i + 1  -- don't forget this bit or the loop will never end
END

 

-- or use a table in memory to create the records and then do one insert at the end
DECLARE @tempTable TABLE
(
    Field1 int
    , Field2 nvarchar(50)

    , Field3 nvarchar(50)
)
SET @i = @startNumber

-- this still does lots of inserts is
WHILE @i <= @endNumber
BEGIN
    -- Do insert here; something like
    INSERT INTO @tempTable
    VALUES (@i, @staticField2, @staticField3)
    SET @i = @i + 1  -- don't forget this bit or the loop will never end
END

INSERT INTO TargetTable (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM @tempTable