Insert data in other table

[Ninadgarude]

Hi,

I work as an Oracle DBA but in my new project I have to handle PL-SQL stuff as well.

Here I have a table A with 500 records. It has ID as PK. Then I have table B which is empty. It has FK A_ID. I want to populate data in table B for each value of ID in A. Please help. I am newbie in PLSQL.

[Tim...]

Hi.

This is kinda difficult to answer because the question is so vague. I know nothing about the structure of the two tables. You've not explained what data should be inserted, apart from the FK value. If I were a developer and you approached me with this, I would tell you to come back when you've actually defined what you want!

Assuming B only has a PK and FK column to populate and assuming the PK column should be populated by a sequence, I would not use PL/SQL at all. I would do this in a single SQL statement.

Code: Select all

INSERT /*+ APPEND */ INTO b (id, a_id)
SELECT b_seq.nextval,
        a.id
FROM   a;

The first requirement of a PL/SQL programmer is they must be great at SQL. As a DBA, you should be great at SQL already, so you should know you don't need PL/SQL to do this.

Cheers

Tim...

[Ninadgarude]

Hi,

I need to enter random data in table B. its nothing to do with what data is present in table A.

I just need to use the relation between two tables.

And for each ID in table A (total 500 ID) , I need to populate data in table B. Structure of both the tables is totally different. Table A has 4 columns whereas table B has 49 columns. I just need random data. Please help.

Even if you give me an example how to do this stuff it will be of great help.

[Tim...]

Hi.

You still don't need PL/SQL to do this.

http://www.oracle-base.com/articles/mis ... andom_data

Cheers

Tim...

[Ninadgarude]

how can i do it using a procedure ?

[Tim...]

Hi.

Well, if you need to do it in a procedure, you can always put the SQL statement into a procedure!

Cheers

Tim...

[Ninadgarude]

Thanks. I am total newbie at this. procedures na ll. I was totally into support environment before this. It will take me some time. Hopefully I will do it.

[Tim...]

Hi.

This is the sort of thing I was thinking about.

Create and populate the first table.

Code: Select all

CREATE TABLE a (
  id  NUMBER,
  CONSTRAINT a_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO a
SELECT level
FROM   dual
CONNECT BY level <= 500;
COMMIT;

Create the new table.

Code: Select all

CREATE TABLE b (
  id           NUMBER,
  a_id         NUMBER,
  small_number NUMBER(5),
  big_number   NUMBER,
  short_string VARCHAR2(50),
  long_string  VARCHAR2(400),
  random_date  DATE,
  CONSTRAINT b_pk PRIMARY KEY (id),
  CONSTRAINT b_a_fk FOREIGN KEY (a_id) REFERENCES a(id)
);

CREATE SEQUENCE b_seq;

Create the procedure.

Code: Select all

CREATE OR REPLACE PROCEDURE populate_b AS
BEGIN
  INSERT /*+ APPEND */ INTO b
  SELECT b_seq.nextval AS id,
         a.id,
         TRUNC(DBMS_RANDOM.value(1,5)) AS small_number,
         TRUNC(DBMS_RANDOM.value(100,10000)) AS big_number,
         DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS short_string,
         DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(100,400))) AS long_string,
         TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date
  FROM   a;
  COMMIT;
END;
/

Run it and test it.

Code: Select all

EXEC populate_b;

SELECT * FROM b ORDER BY id;

if you want a quick response, you should provide all the setup for these types of questions, including all the table creation scripts and any insert statements to set up example data.

Cheers

Tim...

[Ninadgarude]

Hi,

I have created the procedure manually.

I wanted to create a loop so that I can populate the data.

ID is PK in table A. A_ID is FK in table B.

I want to populate 500 records for each ID in table A.

Can you please help ?

[Tim...]

Hi.

That would be something like this then.

Code: Select all

CREATE OR REPLACE PROCEDURE populate_b AS
BEGIN
  FOR i IN (SELECT id FROM a) LOOP
    FOR j IN 1 .. 500 LOOP
      INSERT INTO b
      VALUES (
        b_seq.nextval,
        i.id,
        TRUNC(DBMS_RANDOM.value(1,5)),
        TRUNC(DBMS_RANDOM.value(100,10000)),
        DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))),
        DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(100,400))),
        TRUNC(SYSDATE + DBMS_RANDOM.value(0,366))
      );
    END LOOP;
  END LOOP;

  COMMIT;
END;
/

That's the last free lunch. So far you've not shown me any indication you've actually even logged in an attempted to solve the problem yourself.

If you have another question I want you to post the code you've written to far, so I can see I'm not just doing it all form you.

Cheers

Tim...

[Ninadgarude]

Hi,

I have two tables Input1 and Input2.

Input1 has primary key ID1 and Input2 has primary key ID2.

I have third table Output which has PK as ID3 and it has relation with Input1 and Input2.
It has foriegn keys for both above tables as Input1_FK and Input2_Fk respectively.

Columns in Input1 are:

Code: Select all

create table input1 (
ID1 Number primary key,
Detail1 Varchar2(25),
Detail2 Varchar2(25),
Detail3 Varchar2(25),
Detail4 Varchar2(25)
);

Columns in Input2 are:

Code: Select all

create table input2 (
ID2 Number primary key,
Collect_date Timestamp(6),
Report Varchar2(100),
Valid CHAR(1)
);

Input1 has 1000 records and Input2 has 500 records.

I want to populate Output with data (only Id) from both Input tables such that per ID1 of Input1, I have 500 records of Input2, so total I can have 500000 rows (500*1000)

Columns of Output:

Code: Select all

create table output (
ID Number primary key,
Input1_ID1 Number,
Input2_ID2 Number,
Comments Varchar2(100),
Result Varchar(20),
Expression Varchar(10)
);

Please let me know how can I do it using cursors ?

Below is what I have created.

Code: Select all

create or replace
PROCEDURE AddinOutput
(
in_Input1 in Input1.id%type,
in_Input2 in Input2.id%type
)as
pragma autonomous_transaction;
BEGIN
INSERT INTO Output
(ID,Input1_ID1,Input2_ID2,Comments,Result,Expression)
VALUES (Input1_ID_SEQ.nextval,in_Input1,in_Input2,'sdvbhdsbvhsbv','hbvhvdhvb','ugfhbchvbfd');
commit;
END;

Code: Select all

create or replace
PROCEDURE PopulateOutput
AS

    cursor c1 is
      select id1
      from input1;

    cursor c2 is
      select id2
      from input2;

BEGIN
 
FOR sinput1 in c1
LOOP
  for elem in 1..500
  loop
    dbms_output.put_line(sinput1.id);
    AddinOutput(sinput1.id);
  end loop; 
   
END LOOP;

  NULL;
END Populateoutput;

[Tim...]

Hi.

First, when you send a questions, rather than describing the tables, actually include the CREATE TABLE statements.
Second, please you the CODE tags around your formatted text. It makes it much easier to read. I've corrected your post to do this.
Third, you need to ask the question in the appropriate forum. This is a development question, so it should really be asked in the development forum.

You really need to read this thread about how to ask a question in this forum: viewtopic.php?f=2&t=6261

back to your question...

You've not populated the test tables. you can do this pretty easily, like this:

Code: Select all

insert into input1 (ID1)
select level
from   dual
connect by level <= 10000;

insert into input2 (ID2)
select level
from   dual
connect by level <= 500;

commit;

I've also created a sequence to populate the PK of the output table.

Code: Select all

create sequence output_seq;

I'm not sure why you are using an autonomous transaction in your example...

If someone asked me to do this I wouldn't use a procedure and I certainly wouldn't take the row-by-row approach because it will be incredibly slow, but following on from your example, I would do this.

Code: Select all

create or replace procedure populate_output AS
begin
  for i1 in (select * from input1) loop
    for i2 in (select * from input2) loop
      insert into output (id, input1_id1, input2_id2)
      values (output_seq.nextval, i1.id1, i2.id2);
      commit;
    end loop;
  end loop;
end;
/

Code: Select all

exec populate_output;
select count(*) from output;

Like I said, it is a really bad solution, but it is what you seem to be asking for...

Cheers

Tim...

[Ninadgarude]

Apologies for the inconvenience caused, I will follow the guidelines before posting the question next time.

Can u let me know, what will be the faster way to do this then ?

[Tim...]

Hi.

The quickest way to do it is to use a single insert statement.

Code: Select all

insert /*+ append */ into output (id, input1_id1, input2_id2)
select output_seq.nextval,
       i1.id1,
       i2.id2
from   input1 i1
       cross join input2 i2;

5000000 rows created.

SQL> commit;
SQL>

Cheers

Tim...

[Ninadgarude]

This is ok if I have to insert only data from other tables ....

how will I insert data for below columns in output table then ?

Comments Varchar2(100),
Result Varchar(20),
Expression Varchar(10)
thats why i used stored procedure ... so that i can use wahtever data i want in below 3 columns.