SQL Server 2012 : create a new table with instant 1000 record that contains three @variable in int and varchar

Question

I want to create a new table with multiple variables: @id as int, @lastname as varchar and @amount as money. The three variables should change in a sequential manner

Example of expected result:

#id      #lastname    #amount
-------------------------------
1        Ben1         100
2        Ben2         101 
3        Ben3         103
.        Ben4         104
.        .            .
1000     Ben1000      .1000

I appreciate everyone's input

Answer 1

Your problem is similar to "How To Create a Tally Table" problem. A sample solution is below:

WITH Tally (n) AS (
     -- 1000 rows
     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
     FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
   )
-- INSERT INTO [TableOfYourChoice]
SELECT n, 'Ben' + CONVERT( VARCHAR, n ), n + 100
FROM Tally;

Note: if you want amount to start from 100, then change n + 100 to n + 99

Example adapted from examples at http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/