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

sayed A

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!

Dave Stokes-MySQL Community Team-Oracle

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?

dvohra21

Output indicates SQL commands do get run.

23 rows in set (0.00 sec)

sayed A

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

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

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

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.