CODEWITHSUNDEEP
sqloracle-databaseinsert
PIJUS_MAGNIFICUS
PIJUS_MAGNIFICUS

Reputation: 37

How to insert data in a table from two not-connected tables SQL ORACLE?

I am working with a Data Base with 3 tables:

CREATE TABLE SALE_DETAIL
( S_SALE NUMBER, 
  S_NUMBER NUMBER, 
  S_ARTICLE VARCHAR2(30), 
  S_COUNT NUMBER
);  
CREATE TABLE SALE
( SALE_ID NUMBER, 
  SALE_COMPANY VARCHAR2(30)
); 
CREATE TABLE SELL
( SELL_ARTICLE VARCHAR2(30), 
  SELL_COUNT NUMBER
);

SALE and SELL are tables not connected to each other and they had some data and SALE_DETAIL is empty:

INSERT INTO SALE (SALE_ID, SALE_COMPANY) VALUES (1, 'Company1');
INSERT INTO SALE (SALE_ID, SALE_COMPANY) VALUES (2, 'Company1');
INSERT INTO SALE (SALE_ID, SALE_COMPANY) VALUES (3, 'Company2');
INSERT INTO SALE (SALE_ID, SALE_COMPANY) VALUES (4, 'Company3');
INSERT INTO SALE (SALE_ID, SALE_COMPANY) VALUES (5, 'Company2');

INSERT INTO SELL (SELL_ARTICLE,SELL_COUNT) VALUES ('ART1', 100);
INSERT INTO SELL (SELL_ARTICLE,SELL_COUNT) VALUES ('ART2', 50);
INSERT INTO SELL (SELL_ARTICLE,SELL_COUNT) VALUES ('ART1', 100);
INSERT INTO SELL (SELL_ARTICLE,SELL_COUNT) VALUES ('ART3', 200);
INSERT INTO SELL (SELL_ARTICLE,SELL_COUNT) VALUES ('ART3', 100);

I would like to fill SALE_DETAIL TABLE with data from SALE and SELL table which are not connected to each other.

  S_SALE = SALE_ID --taken from SALE table

  S_NUMBER = ROWID (sequential number starting from 1)   

  S_ARTICLE = SELL_ARTICLE -- taken from SELL table

  S_COUNT = SELL_COUNT  -- taken from SELL table

If SALE and SELL table were connected ...I tried this code

INSERT INTO SALE_DETAIL(S_SALE,S_NUMBER,S_ARTICLE,S_COUNT)
SELECT s.SALE_ID, ROWID, d.SELL_ARTICLE, d.SELL_COUNT FROM SALE s JOIN SELL d
ON (here I could code the connection beetween tha tables)

but How can I do it if they are not connected?

Upvotes: 0

Views: 46

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Join them virtually. How? For example, using row_number function, you can create a common column which is then used in join. Something like this:

SQL> insert into sale_detail (s_sale, s_number, s_article, s_count)
  2  with
  3  t_sale as
  4    (select sale_id,
  5            row_number() over (order by sale_id) rn
  6     from sale),
  7  t_sell as
  8    (select sell_article,
  9            sell_count,
 10            row_number() over (order by rowid) rn
 11     from sell)
 12  select a.sale_id,
 13         a.rn,
 14         e.sell_article,
 15         e.sell_count
 16  from t_sale a join t_sell e on e.rn = a.rn;

5 rows created.

Result:

SQL> select * from sale_detail;

    S_SALE   S_NUMBER S_ARTICLE                         S_COUNT
---------- ---------- ------------------------------ ----------
         1          1 ART1                                  100
         2          2 ART2                                   50
         3          3 ART1                                  100
         4          4 ART3                                  200
         5          5 ART3                                  100

SQL>

If number of rows in sale and sell tables isn't equal, then use outer join (you'll first have to find which table has more rows so that you'd know what to do).

Upvotes: 1

Related Questions