Oracle Insert of multiple lines

Asked

Viewed 12,423 times

1

I need to input multiple records into a table where only one of them will change, the others are constant.

For example:

Insert into processo (ID, status, descricao) values (1, 'X01', 'lorem ipsum dolor')

I have to do hundreds of Inserts like the one above, where the status and description will always be the same but for different ID’s.

Can I do this with a single Insert or need to create a function? How?

sql oracle

2 answers

2


Use the INSERT ALL:

INSERT ALL
  INTO processo (id, status, descricao) VALUES (1, 'X01', 'lorem ipsum dolor')
  INTO processo (id, status, descricao) VALUES (2, 'X02', 'lorem ipsum dolor2')
  INTO processo (id, status, descricao) VALUES (3, 'X03', 'lorem ipsum dolor3')
SELECT * FROM dual;

You can also make Insert in other tables if you want :)

  • Is this the only form with Insert? I’ll need hundreds of lines changing the ID only.

  • With Insert yes, amigo..... The other way would be through pl/sql.

1

If you just want to increment the ID from 1 to 1, you can do a Stored Procedure(command set) with a while and the Insert. For example:

CREATE TABLE tbCliente(
 id int NOT NULL AUTO_INCREMENT,
 numero int,
 nome varchar(50),
 PRIMARY KEY(id)
);

DELIMITER $$
DROP PROCEDURE IF EXISTS myProc$$
CREATE PROCEDURE myProc()
BEGIN
DECLARE i INT default 1;
WHILE i<=100 DO
    INSERT INTO tbCliente(nome,numero) values ('Nomes',i);
    SET i=i+1;
END WHILE;
END$$

DELIMITER ;
call myProc();

With this code the i value in each Insert from 1 to 100 will be increased by 1, where 'i' refers to the field 'number' of the table tbClient. The DELIMITER where it says for sql, where the Procedure code starts and ends. call muProc() will run your past, then it is only necessary to select to see if everything went right.

Remember to run DELIMITER$$ with Procedure and then DELIMITER; along with call myproc().

  • Actually it is not sequential. The Ids are from another query and needed to give an input in another table that has this ID as FK. I know it’s possible with PL/SQL, but as I didn’t end up connecting with an application just to create a function to do this.

Browser other questions tagged sql oracle

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