Insert into from another table

Question

Need help with this sql command.

Tables:

category ( category, id_category, salary, year)
oldcategory ( year, category, salary )

How could I get the data on oldcategory and insert it into category while creating at the same time a number id for each different category on id_category?

INSERT INTO category(category, id_category, salary, year)
SELECT DISTINCT category, () , salary, year
FROM oldcategory
WHERE category IS NOT NULL AND salary IS NOT NULL AND year IS NOT NULL

You need to create a sequence to autogenerate the id_category value. Read this: http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle — mvlaicevich, Feb 14 '16 at 23:25
Welcome to the site! To format text as code make a selection, then click the [{ }] button above the text area. Alternatively, you could prefix the line with four spaces. Make sure there is a blank line ahead of the block that you are formatting as code. — Sergey Kalinichenko, Feb 14 '16 at 23:25

score 0 · Accepted Answer · answered Feb 15 '16 at 02:34

0

Consider a correlated count aggregate for sequential numbers by alphabetical order of category:

INSERT INTO category(category, id_category, salary, year)
SELECT DISTINCT 
       category, 
       (SELECT Count(*) FROM 
                        (SELECT DISTINCT deepsub.category
                         FROM oldcategory deepsub) sub
        WHERE sub.category <= oldcategory.category) AS id_category, 
       salary, year
FROM oldcategory
WHERE category IS NOT NULL 
  AND salary IS NOT NULL 
  AND year IS NOT NULL

answered Feb 15 '16 at 02:34

Parfait

104,375
17
94
125

Thanks, how would it be if instead of doing it by alphabetical order, you assign a number to each new different category you find? – Miguel Lopez Feb 15 '16 at 09:22
This solution does assign number to each distinct category. You need some kind of order to define the number sequence. Please advise if you have some other in mind other than alphabetical. Does `oldcategory` carry IDs? – Parfait Feb 15 '16 at 19:01
I finished doing it with CASE although it required lot of writing but worked fine. Thanks. – Miguel Lopez Feb 17 '16 at 22:26

score 0 · Answer 2 · answered Feb 15 '16 at 04:59

INSERT INTO CATEGORY(CATEGORY, ID_CATEGORY, SALARY, YEAR)
SELECT DISTINCT A.CATEGORY,DENSE_RANK() OVER(ORDER BY A.CATEGORY) AS ID_CATEGORY,
A.SALARY,A.YEAR FROM OLDCATEGORY AS A
WHERE A.CATEGORY IS NOT NULL AND A.SALARY IS NOT NULL AND A.YEAR IS NOT NULL
ORDER BY A.YEAR

Insert into from another table

2 Answers2