I need to insert 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 make hundreds of inserts like the one above, where the status and description will always be the same but for different ID's.
Can you do this with a single insert or do I need to create a function? How?
Use 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 insert into other tables if you want:)
If you just want to increment the ID by 1 in 1, you can do a Stored Procedure with a while and insert. As an 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 value i in each insert from 1 to 100 will be incremented by 1 in 1, where 'i' refers to the 'number' field of the tbCustomer table. The DELIMITER where it says to sql, where the procedure code starts and ends. call muProc () will execute your procedure, then you just need to select to see if everything worked out.
Remember to execute DELIMITER $$ together with the procedure and then the DELIMITER; along with call myproc ().