Insert for each record found - PL/SQL

Asked

Viewed 190 times

0

good morning.

I’m new to Pl/sql and I need to insert 110 records (Number 1 to 110) in a table ( table: stick) for each ID of the court table.

Insert would be more or less:

INSERT INTO vara(vara_id,foro_tribunal_id,nome) 
VALUES (VARA_SEQ.nextval,"TODOS OS FOROS TRIBUNAIS DA TABELA FORO_TRIBUNAL",'1 (DE 1 A 110)');

I have approximately 97,000 records in the court table, would give approximately 10,670,000 Inserts.

What would be the best way to go through all the court table records and enter the 110 values for each record found ?

sql oracle pl-sql procedure oracle12c

  • For each pair of data you want to enter 110 equal records only by varying the generated id?

1 answer

0

Try to do that.

INSERT INTO VARA (vara_id, foro_tribunal_id,nome)
SELECT id, nome FROM foro_tribunal

The oracle will take the data from select and do the Insert.

If necessary you can do a Where in SELECT.

In this article here there are other examples.

  • Oops. Thanks for the expensive answer, but that way it wouldn’t work because the sub-consumption would return more than one line. But Thanks anyway !

  • But here the idea is to exactly insert everything that comes from the sub-concession.

  • I came to test this way, but of the error stating that the subconsulta returns more than one line.

  • @Gedanmagalhães It is possible that your ID is missing from a Quence. What you could do is add a Trigger in Before Insert by adding the ID with the next value of Quence.

Browser other questions tagged sql oracle pl-sql procedure oracle12c

You are not signed in. Login or sign up in order to post.