Forum Stats

  • 3,758,922 Users
  • 2,251,475 Discussions
  • 7,870,433 Comments

Discussions

I have a script file which the sql command line . but it does not execute

sayed A
sayed A Member Posts: 130 Blue Ribbon

The script creates the sql command to copy a table from one database to another provided the table does not exists in the target database. The query checks if the table does not exists in sakila1 and it exist in sakila then table should be created.

When run on mysql command prompt the below query -->

=============

SELECT concat('CREATE TABLE if does not exists sakila1.', TABLE_NAME, ' like sakila.', TABLE_NAME, 


';') FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'sakila'

===========================================

it just writes the following sql command

CREATE TABLE if does not exists sakila1.actor like sakila.actor;                      |

| CREATE TABLE if does not exists sakila1.actor_info like sakila.actor_info;                 |

| CREATE TABLE if does not exists sakila1.address like sakila.address;                    |

| CREATE TABLE if does not exists sakila1.category like sakila.category;                   |

| CREATE TABLE if does not exists sakila1.city like sakila.city;                       |

| CREATE TABLE if does not exists sakila1.country like sakila.country;                    |

| CREATE TABLE if does not exists sakila1.customer like sakila.customer;                   |

| CREATE TABLE if does not exists sakila1.customer_list like sakila.customer_list;              |

| CREATE TABLE if does not exists sakila1.film like sakila.film;                       |

| CREATE TABLE if does not exists sakila1.film_actor like sakila.film_actor;                 |

| CREATE TABLE if does not exists sakila1.film_category like sakila.film_category;              |

| CREATE TABLE if does not exists sakila1.film_list like sakila.film_list;                  |

| CREATE TABLE if does not exists sakila1.film_text like sakila.film_text;                  |

| CREATE TABLE if does not exists sakila1.inventory like sakila.inventory;                  |

| CREATE TABLE if does not exists sakila1.language like sakila.language;                   |

| CREATE TABLE if does not exists sakila1.nicer_but_slower_film_list like sakila.nicer_but_slower_film_list; |

| CREATE TABLE if does not exists sakila1.payment like sakila.payment;                    |

| CREATE TABLE if does not exists sakila1.rental like sakila.rental;                     |

| CREATE TABLE if does not exists sakila1.sales_by_film_category like sakila.sales_by_film_category;     |

| CREATE TABLE if does not exists sakila1.sales_by_store like sakila.sales_by_store;             |

| CREATE TABLE if does not exists sakila1.staff like sakila.staff;                      |

| CREATE TABLE if does not exists sakila1.staff_list like sakila.staff_list;                 |

| CREATE TABLE if does not exists sakila1.store like sakila.store;                      |

+------------------------------------------------------------------------------------------------------------+

23 rows in set (0.00 sec)


The SQL command does not get executed.

I need to create the required tables in sakila1 by running the above mentioned query instead of just writing for the command to create table.

Could someone help me!

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 361 Employee

    Can you explain what you are trying to do? The command runs as you asked. Are you trying to get the server to act on all those create statements?


    Dave Stokes

    MySQL Community Manager

  • dvohra21
    dvohra21 Member Posts: 14,227 Gold Crown

    Output indicates SQL commands do get run.

    23 rows in set (0.00 sec)

  • sayed A
    sayed A Member Posts: 130 Blue Ribbon

    Sorry for the delay.

    What i am trying to see that table needs created from the script. What modification I need to put in the script so that tables which

    does not exist in the target table but exist in the source table should be created with the same structure and triggers, procedures and indexes.

  • dvohra21
    dvohra21 Member Posts: 14,227 Gold Crown

    What is the ".. if does not exists.." clause used in all statements? Sorry for the pun, but such a clause does not exist. The clause to use is

    CREATE TABLE ... IF NOT EXISTS...

    Reference:

    https://dev.mysql.com/doc/refman/5.6/en/create-table.html

    https://dev.mysql.com/doc/refman/8.0/en/create-table.html

  • sayed A
    sayed A Member Posts: 130 Blue Ribbon

    yes you are correct.

    But still the problem remains. No table is created from the script. We need all the tables created

    i have made a procedure

    DELIMITER $$


    USE `sakila1`$$


    DROP PROCEDURE IF EXISTS `Table_Creation`$$


    CREATE DEFINER=`root`@`localhost` PROCEDURE `Table_Creation`()

    BEGIN

    SELECT GROUP_CONCAT('CREATE TABLE if not exists sakila1.', TABLE_NAME,' ', 'select * from ','sakila.', TABLE_NAME, ';') FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'sakila' INTO @sql;

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DROP PREPARE stmt; 

    END$$


    DELIMITER ;


    Still the solution is not in sight.

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 361 Employee
    edited May 24, 2021 11:47AM

    Your query SELECT concat('CREATE TABLE if does not exists sakila1.', TABLE_NAME, ' like sakila.', TABLE_NAME, ';') FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'sakila' is running and providing exactly what you asked for. The output is strings that could be feed into a MySQL client to create tables but by itself it will create tables.

    Your options include saving the output of your query into a file and then executing that file, writing a CTE to collect the table names and then create the tables, or use a subquery to collect the table names and then create the tables.

    Dave Stokes

    MySQL Community Manager