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
31

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
8
  • +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
    Commented Dec 29, 2009 at 19:43
  • @James, you're right, the mysql(1) man page does strongly suggest that this is an interactive-mode command only.
    – pilcrow
    Commented Dec 29, 2009 at 20:16
  • 1
    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...
    – Roland Bouman
    Commented Dec 29, 2009 at 20:36
  • @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.
    – pilcrow
    Commented Dec 5, 2011 at 14:51
  • 1
    Does not work in MySQL Workbench 6.1, but does work from the command line on my Windows 8.1 installation.
    – MM.
    Commented Aug 5, 2014 at 20:10
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
  • 1
    This doesn't answer the question.
    – David
    Commented Oct 8, 2012 at 21:11
  • 3
    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.
    – gary.affonso
    Commented Oct 11, 2012 at 17:41
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
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.