Using SQL to create sequential list of item numbers

THBA
THBA used Ask the Experts™
on
I need to create a SQL that will fill in the empty id numbers. For example, Table items
Items
id     description
1     red pencil
2     green pencil
5     red pen
6     black pen
8     paper
My query needs to return:
id    description
1     red pencil
2     green pencil
3
4
5     red pen
6     black pen
7
8     paper

I am stumped on this simple issue. I am trying not to use stored procedures.
This SQL will be used in SSRS ( SQL Server Reporting Services).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
create a table with one field, ID, tblID
fill the table with continuous numerical values
then create a query like this

SELECT tblID.ID, yrTable.description
FROM tblID LEFT JOIN yrTable ON tblID.ID = yrTable.ID;
Commented:
informix supports create sequence (see http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls264.htm).

for example

 CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 1 MAXVALUE 10000 NOCYCLE NOCACHE;

then you can make insert by

 INSERT INTO TABLE1 (id, description) values (myseq.nextval, 'some description');

Author

Commented:
I break down and simplified the problem too much.   Here is what I'm trying to do:
I have list of items (actually they are upc codes). There are literally thousands. Let's limit it to 20 for easier discussion. Now, those items are 11 digits long.  I am incrementing by 100000
Look at the first attached file:  Those items are in the database. You can see  there are no 20700100000
The second attached file: those data is what I am trying to get. If there are  id's not in the database, then I want to include the ids without a description.
Technically, you answered the question because I did not ask the right question. Will you be able to help me a little more?

itemsInTable.JPG
itemsInTable.JPG
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
This is the second image.
FormatIwant.JPG

Commented:
is that an action you have to make once? or did you get the above table (without records where description is empty) periodically and so have to repeat inserting the missing numbers in the table again?

Commented:
Maybe the following works. you would need to copy the insert statement 10000 times or write a stored procedure that could do the same in a loop.
create sequence seq1 start with 20700100000 increment by 100000 nocycle nocache;
create table table2 (id number(11) not null, description varchar2(64));

-- repeat the following statement until you have all records you need in table2 
insert into table2  (id) values (seq1.nextval);

-- fill all existing descriptions from table1
update table2 set (description = (select table2.description from table1 where table2.id = table1.id);

-- finally drop table1 and rename table2 to table1
drop table1;
alter table table2 rename to table1;

Open in new window

Commented:
at the update statement is a ) missing at end.

Author

Commented:
Asked the wrong question. Technically, they did answer the question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial