If i have this:
CREATE TABLE ftable (
id INT,
fvalue VARCHAR(14)
);
INSERT INTO ftable VALUES (1,'tableB'),(2,'tableA');
CREATE TABLE tableA (
value VARCHAR(14)
);
SELECT @tmp:=fvalue FROM ftable WHERE id=2;
How do I make it so I can do this:
INSERT INTO @tmp VALUES ('buhambug');
Becuase as far I know that throws a mysql error.Can someone show me a sqlfiddle of the solution? Or maybe I'm thinking about this the wrong way?
You need to use dynamic SQL to use a variable as an object name:
SET @tmp = (SELECT fvalue FROM ftable WHERE id=2);
SET @SQL = CONCAT('INSERT INTO ',@tmp,' VALUES (''buhambug'')');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
SELECT to assign the value to @tmp so it generates a record set. In this example I have used SET instead of SELECT so now there is only one recordset which shows BahambugYou can't do in static sql. You can do it in stored procedure:
delimiter $$
drop procedure if exists test_call$$
create procedure test_call(table_in varchar(100))
begin
set @q = concat("select * from ", table_in);
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end$$
delimiter ;
call test_call('TeableA');
drop procedure if exists test_call;
In general dynamic read from dynamic tables is not a good decision
