Search code examples
sqlautofillsequential

How can I insert multiple ranges of sequential numbers to 1 column in SQL?

I have created an empty column "ZipCodes" in a table. I want to autofill this column with the following numbers: 1000-3900 4000-9999

So all of these sequential numbers need to be in 1 column starting with 1000 and ending with 9999 but I don't want to include the range 3901-3999. How can I do this in SQL?

Solution

  • You can do with using CTE

    ;WITH temp_recoreds AS
(
SELECT 1000 AS ZipCode
UNION ALL
SELECT  CASE WHEN ZipCode=3900 THEN 4000 ELSE ZipCode+1 END AS ZipCode FROM temp_recoreds WHERE ZipCode<=9998
)
INSERT
      INTO Your_table(ZipCode)
      SELECT  z.ZipCode
      FROM temp_recoreds AS z
OPTION (MAXRECURSION 0);