Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert data from Table A into Table B how can I do that?

Status
Not open for further replies.

harfri

Programmer
Mar 5, 2010
17
NL
I have table A with my account balance and i would like to use this data to insert them into my new table B. In this table I would like to see my account balance per day.

Is this possible with Oracle sql? Is there a insert statement or a function which i can use?

I would be very happy if someone can help me.

Table A

Date from Date until Balance
1-1-2018 5-1-2018 125

Table B
Date Balance
1-1-2018 125
2-1-2018 125
3-1-2018 125
4-1-2018 125
5-1-2018 125


 
This is a bit klugey, but it should work:

1. Create table B:
Code:
CREATE tableB AS SELECT * FROM tableA;

Then add the rest of the records:
Code:
DECLARE
   l_date DATE := '1-JAN-2018';
BEGIN
   FOR i IN 1..364 LOOP
      l_date := l_date + 1;
      INSERT INTO tableB VALUES(l_date, 125);
   END LOOP;
   COMMIT;
END;

This should at least get you within striking distance of where you want to be.
 
Code:
CREATE TABLE Tableb
AS
    SELECT B.Date_from + (A.Rnum - 1), Balance
    FROM (SELECT ROWNUM Rnum
          FROM (SELECT 1
                FROM DUAL
                CONNECT BY LEVEL <=
                           (SELECT (Date_to - Date_from) + 1 FROM Tablea))) A,
         Tablea B;

Bill
Lead Application Developer
New York State, USA
 
My apologies - I didn't read your post carefully and was responding to the one you put up in a different forum. This is moot, however, since Bill has provided us with a very elegant solution. One note though - you will need to provide a column name for the expression "B.Date_from + (A.Rnum - 1)" before this will work (at least, that was my experience).

Nicely done, Bill! Thank you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top