Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1967
  • Last Modified:

sql query to create new records from 0001 to 9999

As the title suggests, i need to create a new table call CardPool

In this table i need the following structure

CardNumber (4 digits unique number) clustered
ActiveStatus (bit) default 0

then in the table i need to have the rows from 0001 to 9999
i,e,
0001
0002
0003
...
9998
9999

Does anyone know the correct script to generate this?
0
websss
Asked:
websss
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are other ways to do ...
;with data as (select 1 n union all select n + 1 from data where n < 9999 )
select right('000' + cast(n as varchar(10)), 4) from data
option (maxrecursion 10000)

Open in new window

;with digits as (select '0' n 
union all select '1'
union all select '2'
union all select '3'
union all select '4'
union all select '5'
union all select '6'
union all select '7'
union all select '8'
union all select '9'
)
select d1.n + d2.n + d3.n + d4.n
 from digits d1, digits d2, digits d3, digits d4
where d1.n + d2.n + d3.n + d4.n <> '0000'
order by 1

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
of course, the above is only the SELECT part, which you can use to actually INSERT into your table
0
 
websssAuthor Commented:
Thanks,
I'm not sure how this works as i cannot see inserting into the CardNumber column?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the syntax, from above, would be in general:
;WITH alias AS ( <some query> )
INSERT INTO yourtable ( <columns> )
SELECT <columns>
  FROM alias

so, taking my first query sample:
;with data as (select 1 n union all select n + 1 from data where n < 9999 )
INSERT INTO CardPool ( CardNumber ) 
select right('000' + cast(n as varchar(10)), 4) 
from data
option (maxrecursion 10000) 

Open in new window

0
 
Scott PletcherSenior DBACommented:
Recursion is more resource-intensive than an inline CROSS JOIN.  A sequential list of numbers is usually called a "tally" table, so I retain that naming for the CTE:

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]
    WHERE
        [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit > 0
)
INSERT INTO dbo.CardPool ( CardNumber, ActiveStatus )
SELECT t.tally, 0
FROM cteTally t
ORDER BY
    t.tally
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now