34

I'm involved is a project to migrate a project from Oracle to MySQL. In Oracle I have the ability to create a SQL script that references or inlcudes other external SQL script files when the batch is run via command line. I Have a script called CreateAllTables.sql that looks like this internally:

@tables\Site.sql
@tables\Language.sql
@tables\Country.sql
@tables\Locale.sql
@tables\Tag.sql

I'm already aware of the MySQL command line "Source" command, but my goal is to invoke a single main .sql script file that includes other scripts via one single command line call like this:

mysql --user=root --password --database=junkdb -vv < CreateAllTables.sql

So my question is how do I do this with MySQL?

Improve this question

3 Answers 3

Reset to default
32

source works for me.

# -- foo.sql
DROP TABLE foo;
source bar.sql

# -- bar.sql
CREATE TABLE bar (i INT NOT NULL);

$ mysql ... < foo.sql

Now table foo is gone and bar is created.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

+1 Interesting, all of the examples I saw seemed to suggest this was a command line function only. I'm still pretty new to MySQL.
@James, you're right, the mysql(1) man page does strongly suggest that this is an interactive-mode command only.
You can do help in the mysql command line client, and see the possible commands understood by the client (not sql server commands, mysql command line commands): mysql> help For information about MySQL products and services, visit: mysql.com For developer information, including the MySQL Reference Manual, visit: dev.mysql.com To buy MySQL Network Support, training, or other products, visit: shop.mysql.com List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. ...etc...
@avok00: May I ask specifically what you mean that "[t]his does not work in scripts"? The OP wanted a batch or scripted solution that used a "top-level" .sql file to include and execute other named .sql files. That's precisely what I show. With minor variation, that's also what the other answer shows.
Does not work in MySQL Workbench 6.1, but does work from the command line on my Windows 8.1 installation.
|
17

Note that the "source" option, above, only works (for me) if the script is run through a mysql client that supports it. (The mysql command-line client referenced in the OP's original question happens to be one of those clients.)

But remember "source" is not one of the many mysql-specific extensions to the sql language. It's a client-command, not a sql statement,

Why do you care?

If you're sending your sql script to the MySQL server via an alternative method (via JDBC's "execSQL", for example) the "source" command will not work for the inclusion of other scripts.

Improve this answer

2 Comments

This doesn't answer the question.
Yah, I know. I would have preferred to simply add a comment to the accepted answer's comment list. There is some confusion in that comment stream and I thought this info would help clarify. But this is a new stackoverflow account and I don't yet have enough cred to comment. (At least I think that's why I can't comment.). So I just posted a new "clarifying" answer instead. Apologies if this offends.
8

You can do a similar thing with source in mysql.

I have inc1.sql with these contents: 

use test;
create table testinc(
   id int  
);

And inc2.sql like this: 

insert into testinc values (1);

and main.sql like this:

source inc1.sql
source inc2.sql

And i can run main.sql like this:

mysql -uroot -pmysql -P3351 -e"Source main.sql"

After that I can verify that it worked by doing this:

mysql> use test;
Database changed
mysql> select * from testinc;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.