SQL query

I'm trying to list the numbers between 515000 and 516000 to insert into an empty table (nvarchar type)
shwelopoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please use this..

Do not worry about the conversion, DB will implicitly convert the INT values to NVARCHAR.

IF the table is already there..

INSERT INTO yourNewTable(Column1,Column2....etc)
SELECT Column1,Column2....etc
FROM yourTable
WHERE NumberCol >= 515000 and NumberCol<= 516000


IF the table is NOT already created

SELECT Column1,Column2....etc
INTO yourNewTable
FROM yourTable
WHERE NumberCol >= 515000 and NumberCol<= 516000
0
Pawan KumarDatabase ExpertCommented:
FOR ORACLE

IF the table is already there..

INSERT INTO yourNewTable(Column1,Column2....etc)
SELECT Column1,Column2....etc
FROM yourTable
WHERE NumberCol >= 515000 and NumberCol<= 516000;

IF the table is NOT already created

CREATE TABLE yourNewTable
AS
(
      SELECT Column1,Column2....etc FROM yourTable
      WHERE NumberCol >= 515000 and NumberCol<= 516000
);
0
Bill PrewCommented:
What DBMS are you using, here is an example in SQL Lite that will generate those numbers.

WITH RECURSIVE
    Series(Number) AS (
        VALUES (515000)
        UNION ALL
        SELECT Number + 1
        FROM   Series
        WHERE  Number < 516000
    )
SELECT s.Number
FROM   Series s
ORDER  BY s.Number;

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Pawan KumarDatabase ExpertCommented:
DO NOT use RECURSION Method..Use SET BASED APPROACH. For one time it should be ok.

FOR SQL SERVER - IF THE TABLE IS NOT THERE

/*Declare Variables*/
DECLARE @Start AS BIGINT = 515000
DECLARE @End AS BIGINT = 516000

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) + (1000*d5.Number)  Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4,
	SingleDigits as d5
)
SELECT 
  @Start + (Number -1) Number
INTO YourNewTableName
FROM Series 
WHERE @Start + (Number -1) <= @End
ORDER BY Number

Open in new window


FOR SQL SERVER - IF THE TABLE IS THERE

/*Declare Variables*/
DECLARE @Start AS BIGINT = 515000
DECLARE @End AS BIGINT = 516000

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) + (1000*d5.Number)  Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4,
	SingleDigits as d5
)
INSERT INTO YourNewTableName
SELECT 
  @Start + (Number -1) Number
FROM Series 
WHERE @Start + (Number -1) <= @End
ORDER BY Number

Open in new window

0
Bill PrewCommented:
In SQL Server you could do:

WITH series AS (
    SELECT 515000 AS num
    UNION ALL
    SELECT num+1 FROM series WHERE num+1<=516000
)
SELECT * FROM series
option (maxrecursion 1000);

Open in new window


»bp
0
PortletPaulfreelancerCommented:
Please always indicate which database you are using. As you can see from the various suggestions the syntax differs somewhat for each dbms. As you refer to "nvarchar type" I presume you are using SQL Server.

You should also always remain aware of  data type conversions, these can be the (unexpected) cause of performance issues, so whilst I don't know why you want a series of integers as nvarchar I would suggest you explicitly convert while generating the wanted numbers.  Note the following "tally table" code block produces 1,000 integers, if you need more than that just follow the pattern to produce more.
DECLARE @Start AS INT = 515000;
DECLARE @End AS INT = 516000;

;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 
              d1s.digit 
            + d10s.digit * 10
            + d100s.digit * 100  /* 1000, add more like this as needed */
            --+ d1000s.digit * 1000  /* 10,000 add more like this as needed */
            AS num
      FROM cteDigits d1s
      CROSS JOIN cteDigits d10s
      CROSS JOIN cteDigits d100s /* 1000, add more like this as needed */
      --CROSS JOIN cteDigits d1000s /* 10,000 add more like this as needed */
      )
INSERT INTO TheEmptyTable
SELECT 
       convert(nvarchar(6),( @Start + Num )) AS column1
FROM cteTally 
WHERE (@Start + Num ) <= @End

Open in new window

0
Bill PrewCommented:
DO NOT use RECURSION

And the reason for that is...?


»bp
0
Pawan KumarDatabase ExpertCommented:
>>And the reason for that is...?
It slows down the things..we need to use SET based approach in all RDBMS's as they are naturally fast in nature as you play with the entire DATASET.. ..But for one time activity anything will do as i mentioned in my last post.
0
Bill PrewCommented:
For generating a sequence of 1000 values the RECURSIVE approach works just as fast and I personally find the code easier to write and understand.


»bp
0
Pawan KumarDatabase ExpertCommented:
Yes you can use..its perfectly alright but it is not the preferred method...for DB engine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.