locked
Temp table with range of values RRS feed

  • Question

  • Can anyone help me with this.

    I wanted to create a temp table and the table should be having the following values

    It should have a specific range of values

    If suppose i wanted values between 13,456,000 to 14,125,000 to be displayed in the temp table .

    ID is the only column it should have.

    There is no table in my database that has these values. These are just random values.

    Thanks for  help

    Friday, July 15, 2011 4:16 PM

Answers

  • I see, we can do

    insert into #Ranges
    select RIGHT(replicate('0',12) + cast(13459999 + n as varchar(12)),12)
    from numbers
    
    where n between 1 and (14125000-13456000)
    
    select #R.InventoryID, case when I.InventoryID IS NULL then 'Not Assigned' ELSE 'Assigned' END as Status
    from #Ranges
    LEFT JOIN Inventory on R.InventoryID = Inventory.InventoryID
    
    

    Using RIGHT trick we append 0 to the beginning of the numbers where numbers are less than 12 digits. I used 12, but you need to use the length of your InventoryID field here.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kalman Toth Saturday, July 16, 2011 1:15 AM
    • Marked as answer by naga1245 Saturday, July 16, 2011 3:35 AM
    Friday, July 15, 2011 9:31 PM
  • Just use a numbers CTE; something like this (from Itzik):

     

    ;WITH
     L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
     L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
     L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
    
    select 13459999 + n
    from numbers

    where
    n between 1 and (14125000-13456000)

     EDIT:

    Added FROM clause to fix error



    • Proposed as answer by Naomi N Friday, July 15, 2011 4:36 PM
    • Edited by Kent Waldrop Friday, July 15, 2011 7:33 PM
    • Marked as answer by naga1245 Saturday, July 16, 2011 3:35 AM
    Friday, July 15, 2011 4:19 PM

All replies

  • Just use a numbers CTE; something like this (from Itzik):

     

    ;WITH
     L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
     L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
     L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
    
    select 13459999 + n
    from numbers

    where
    n between 1 and (14125000-13456000)

     EDIT:

    Added FROM clause to fix error



    • Proposed as answer by Naomi N Friday, July 15, 2011 4:36 PM
    • Edited by Kent Waldrop Friday, July 15, 2011 7:33 PM
    • Marked as answer by naga1245 Saturday, July 16, 2011 3:35 AM
    Friday, July 15, 2011 4:19 PM
  • Tried this solution having an error

    Invalid column name 'n'

     

    Friday, July 15, 2011 4:56 PM
  • Kent missed FROM Numbers at the very last statement.

    So,

    select 13459999 + n
    where n between 1 and (14125000-13456000)
     FROM Numbers
    
    should work.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, July 15, 2011 5:05 PM
  • Excellent and thank you very much .But i have another question

     

    If my Data type should be a char datatype and i am looking for the same numbers.

    What changes do i have to make to my query

    Will this be possible using char data type. I am thinking it could be possible

    Friday, July 15, 2011 6:46 PM
  • Same query would work fine for Char data type too. Implicit conversion.
    Friday, July 15, 2011 7:04 PM
  • Hello

    Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

    MSDN Transact SQL Forum Posting Guidelines:

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

          Posted by Clifford Dibble

          http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

          Posted by Phil Brammer

    Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

    If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.


    Friday, July 15, 2011 7:37 PM
  • Hi Naomi

    I will try to explain the problem in detail.

    I have a table Inventory with ID as one of the column. There are 1.2 billion rows in the table with different ID's.Here ID is a Varchar datatype

    Now I am given a range of ID's. I have to see if the ID's in the given range has been assigned to the inventory .

    If The ID is assigned to the inventory.It should say assigned and if it is not it should say available

    The output should say something like this

    ID              Status

    12345610    assigned

    12345611    available

    12345612    assigned

    12345613    assigned

    12345614    available


    The range is between 12345610 and 12345614 . So every value between this numbers should be checked for the ID column in Inventory table.

    The range will be actually 10 million or so.

    All the other columns in the table are not needed i guess I hope you understand my problem

    Friday, July 15, 2011 7:46 PM
  • I see, that's a bit tricky, but still:

    create table #Ranges (InventoryID varchar(12) primary key)
    
    ;WITH
     L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
     L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
     L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
    insert into #Ranges
    select 13459999 + n
    from numbers
    
    where n between 1 and (14125000-13456000)
    
    select #R.InventoryID, case when I.InventoryID IS NULL then 'Not Assigned' ELSE 'Assigned' END as Status
    from #Ranges
    LEFT JOIN Inventory on R.InventoryID = Inventory.InventoryID
    

    I'm not sure about performance, but that's the best I can think of quickly.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, July 15, 2011 7:52 PM
  • I think it is working but i have a question though.

    There are some values in my ID column like 0744435 to 0744670

    So it will not take into account the zero before 7 right?

    the output shows me just 744435 and it says available even when i enter 0744435.

    If i am using just numbers in the where condition it is working good that is

    if i use where n between  '1' and ('14125000'-'13456000') It throws me an error Operand data type varchar is invalid for subtract operator

     

    Friday, July 15, 2011 8:28 PM
  • I see, we can do

    insert into #Ranges
    select RIGHT(replicate('0',12) + cast(13459999 + n as varchar(12)),12)
    from numbers
    
    where n between 1 and (14125000-13456000)
    
    select #R.InventoryID, case when I.InventoryID IS NULL then 'Not Assigned' ELSE 'Assigned' END as Status
    from #Ranges
    LEFT JOIN Inventory on R.InventoryID = Inventory.InventoryID
    
    

    Using RIGHT trick we append 0 to the beginning of the numbers where numbers are less than 12 digits. I used 12, but you need to use the length of your InventoryID field here.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kalman Toth Saturday, July 16, 2011 1:15 AM
    • Marked as answer by naga1245 Saturday, July 16, 2011 3:35 AM
    Friday, July 15, 2011 9:31 PM
  • Hi, Naomi

    I got the output exactly how i wanted it.

    But Instead of  select RIGHT(replicate('0',12) + cast(13459999 + n as varchar(12)),12)

    I Used 

    SELECT

    RIGHT(REPLICATE('0',9)+LTRIM(RTRIM(1345999 +n )),9) and it worked

    Thank you all for your help

     

     

    Friday, July 15, 2011 11:37 PM