MySQL values statement as a parameter?

Issue

is it possible to insert into a MySQL table with the whole VALUES block of code being a parameter?

Parameter would be:

  ("(638) 833-5496","[email protected]","Spain"),
  ("(293) 742-0229","[email protected]","Belgium"),
  ("1-265-156-4304","[email protected]","Ireland"),
  ("1-833-780-2553","[email protected]","France"),
  ("(619) 691-0656","[email protected]","Costa Rica");

Insert statement would be

INSERT INTO `myTable` (`phone`,`email`,`country`)
VALUES
  {parameter}

Is it possible to do as an Insert statement, stored procedure, or anything?

Solution

I was able to solve this by utilizing the EXECUTE command and passing the string as a variable and then compiling it into one SQL statement.

CREATE DEFINER=`admin`@`%` PROCEDURE `insert_string_storedprocedure`(bubble_variable text)
BEGIN

set @insert_string = "INSERT INTO `myTable` 
(`phone`,`email`,`country`)
VALUES
(insert_statement)
AS new
ON DUPLICATE KEY UPDATE
email = new.email;";

set @fullcommand = REPLACE(@insert_string, '(insert_statement)', bubble_variable);

PREPARE stmt FROM @fullcommand;
EXECUTE stmt;

END

Answered By – Austin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published