THBA
asked on
Using SQL to create sequential list of item numbers
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is the second image.
FormatIwant.JPG
FormatIwant.JPG
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?
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;
at the update statement is a ) missing at end.
ASKER
Asked the wrong question. Technically, they did answer the question.
ASKER
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