How to Create View or Table from Prepared Statement (Select Query)

Question

I am trying to create View from the following prepared statement:

CREATE VIEW myview AS -- this line is not working
SELECT CONCAT(GROUP_CONCAT('SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total FROM mydb.source_table' SEPARATOR '\n UNION ALL \n'),'\nORDER BY Total ASC')
INTO @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' 
    AND TABLE_NAME   = 'source_table'
    AND COLUMN_NAME NOT IN ('ID', 'Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

I am still confused with handling group_concat syntax.

I just need help on how to create View with the above prepared statement.

UPDATE 1: Stored procedure

As suggested by @nbk, I have to create into 5 stored procedures in single column to be able the data and use it to CREATE TABLE query. Here's the stored procedure version of the above codes.

CREATE DEFINER=`admin`@`%` PROCEDURE `sp_result`()
BEGIN
    SELECT CONCAT(GROUP_CONCAT('SELECT ''', COLUMN_NAME,''' MyColumns FROM mydb.source_table' SEPARATOR '\n UNION ALL \n'), '\n LIMIT 0, 1 \n')
    INTO @sql
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'mydb' 
        AND TABLE_NAME   = 'source_table'
        AND COLUMN_NAME NOT IN ('ID', 'Name');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
END

Here's the result of this stored procedure:

+-----------+
| MyColumns |
+-----------+
| Abc       |
+-----------+

In order this approach to be useful to me, I want the above result to be the one of the column of my CREATE TABLE query:

Here are my attempts but no luck:

First attempt: Separate Query

CREATE TABLE my_table AS SELECT id, name, mydb.sp_result() FROM source_table; -- Error Code: 1305. FUNCTION project_x.best_vendor1_name does not exist

Second attempt: Inserted at the last part of the stored procedure

CREATE TABLE my_table AS SELECT @sql FROM source_table; -- no effect

Here, I really do not know how CREATE TABLE using the stored procedure returned data.

UPDATE 2: Illustrate Encapsulation

CREATE DEFINER=`root`@`%` PROCEDURE `proc_column_sum`()
BEGIN
DROP TABLE IF EXISTS table2;
SELECT CONCAT('
    CREATE TABLE table2 AS (',GROUP_CONCAT('
        SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total 
        FROM testdb.products ' 
        SEPARATOR '\n UNION ALL \n'), -- runtime syntax error somewhere here
    '\n ORDER BY Total ASC)') -- missing closing single quote right after ASC
INTO @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' 
    AND TABLE_NAME   = 'products'
    AND COLUMN_NAME NOT IN ('ID', 'Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END

@nbk I see your suggestion as opportunity to change my approach, if indeed not possible. Please see my update (UPDATE 1) in my question and I need little help with CREATE TABLE using the STORED PROCEDURE you suggested. — RickyBelmont, Aug 02 '20 at 00:04

nbk · Accepted Answer · 2020-08-03T09:58:23.470

1

You must put the CREATE TABLE in your prepared statement.

this example uses a temporary table only for demonstration purposes

CREATE DEFINER=`root`@`%` PROCEDURE `proc_column_sum`()
BEGIN
SELECT 
CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (',GROUP_CONCAT('SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total FROM testdb.products ' SEPARATOR '\n UNION ALL \n'),'\nORDER BY Total ASC)')
INTO @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' 
    AND TABLE_NAME   = 'products'
    AND COLUMN_NAME NOT IN ('ID', 'Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END

you call it then

call proc_column_sum();
SELECT * FROM table2

But with this approach you have call every time the procedure to get the newest data.

I can' figure out, what went wrong in your query

CREATE DEFINER=`root`@`%` PROCEDURE `proc_column_sum`()
BEGIN
DROP TABLE IF EXISTS table2;
SELECT CONCAT('
    CREATE TABLE IF NOT EXISTS table2 AS (',GROUP_CONCAT('
    SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total 
    FROM testdb.products ' 
    SEPARATOR '\n UNION ALL \n')
    ,'\nORDER BY Total ASC)')
INTO @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' 
    AND TABLE_NAME   = 'products'
    AND COLUMN_NAME NOT IN ('ID', 'Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END

edited Aug 03 '20 at 09:58

answered Aug 02 '20 at 08:25

nbk

45,398
8
30
47

Can I use CREATE TABLE instead of CREATE TEMPORARY TABLE? Secondly, I am getting some error at runtime and still trying to figure-out in the syntax. I am sure I got your code correctly. – RickyBelmont Aug 02 '20 at 13:10
Here's the error I am getting: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT 'Sed quidem aspernatur quisquam ut.' Vendor, SUM(`Sed quidem a' at line 2 – RickyBelmont Aug 02 '20 at 13:11
1

yes you use a table, but you have to drop it first.you have strange cpolumnnames and you made an error transfering the code, to debug you siable everything from prepare and make a select @sql; so you see the hole thing and correct it in the code, if there is a probolem – nbk Aug 02 '20 at 13:20
I am getting the group_concat correctly when running the @sql. I think this error emanates somewhere the new encapsulation `CREATE TABLE`. Your code above doesn't return syntax error at design time instead it returns an error `near 'UNION ALL` at runtime. I going to provide an update 2 above to illustrate the encapsulation of the codes. – RickyBelmont Aug 02 '20 at 23:13
I have updated the question (UPDATE 2) to illustrate my version of encapsulation and commented some suspected error source. – RickyBelmont Aug 02 '20 at 23:55
`group_concat` is fine. I could see the text and it works fine. I am trying this time to see the `concat text` at runtime to check the syntax but I do not know how? – RickyBelmont Aug 03 '20 at 00:15
I can' fgure out what os wrong with you try, i tokk mine and formated it like yours but still it runs, your gives errors, but i can't figure out why – nbk Aug 03 '20 at 09:59
See [fiddle](https://www.db-fiddle.com/f/27Rv4Jz8CEm7r8TxYLVfC6/0) (fake products table created, built query text printed, prepared statement commented) - it appears that the built SQL text is synthactically wrong. – RickyBelmont Aug 03 '20 at 10:29
1

No it has nothing to do with syntax, the problem is that mysql 5.7 doesn't allow that sort of thing, in mysql 8 it runs without a problem. mariadb also doesnÄ'have a problem with it – nbk Aug 03 '20 at 13:39
yes! I agree with you. I tried your code with mysql 8. it works! However, can you check this particular part of your code `,'\nORDER BY Total ASC)')` there's a missing single quote right after `ASC` and I suspect 8.0 ignore this minor error but 5.7 didn't. – RickyBelmont Aug 03 '20 at 23:15
I am contemplating on upgrading MySQL to 8.0. Is it wise to do it now? – RickyBelmont Aug 04 '20 at 00:57
Its done its working now with 8.0 version and didn't encounter any issue. Can you post your answer here [Create Table on Prepare Statement MySQL](https://stackoverflow.com/questions/63224559/create-table-in-prepare-statement-in-mysql?noredirect=1#comment111800853_63224559) so I could mark answered and close the case. – RickyBelmont Aug 04 '20 at 03:44

How to Create View or Table from Prepared Statement (Select Query)

1 Answers1