How can I execute an SQL file, and save the results to a txt file in one command?

Question

I am using an Ubuntu terminal. I've found answers about running .sql files, and how to save query results to an external file. How can I do them both at the same time?

I've tried this command: source /path/to/file.sql into outfile /path/to/outfile.txt.

I've tried to add into outfile /path/to/outfile.txt directly into file.sql and running source /path/to/outfile.sql.

I've tried mysql -u <username> -p <database> file.sql > results.txt. If I switch file.sql with something like this -e "select * from myTable", then it works fine.

How can I do this?

EDIT: Here is file.sql

select myID from Players where score > 80;

It's a simple query, but if I can figure this thing out, I can try to do bigger queries.

Possible duplicate of stackoverflow.com/questions/21253704/… — Krishnadas PC
– Krishnadas PC, Commented Apr 2, 2016 at 2:27

Community · Accepted Answer · 2020-06-20 09:12:55Z

18

Your answer is here: http://dev.mysql.com/doc/refman/5.7/en/mysql.html

You can execute SQL statements in a script file (batch file) like this:

shell> mysql db_name < script.sql > output.tab

Try this: mysql -u username -p database < file.sql > results.txt

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 2, 2016 at 2:26

fdglefevre

6924 silver badges15 bronze badges

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

Comments

student2792 · Accepted Answer · 2016-04-02 02:53:16Z

1

@FLefèvre has a solution but I couldn't get it to work. It just outputted a MySQL help page where it shows the different commands.

Add this to the sql file: .... into outfile '/path/to/outfile.txt'. Single or double quotations on the path. And then in the MySQL shell, just do source /path/to/file.sql.

answered Apr 2, 2016 at 2:53

student2792

1411 gold badge2 silver badges8 bronze badges

1 Comment

fdglefevre Over a year ago

My answer works I tested it. The problem with your solution is that you need to modify the file content each time you want to change the results file. It's not a problem of course if you never reuse it... :)

Rjazhenka · Accepted Answer · 2018-07-17 13:55:44Z

1

mysql -u root -p -e"`cat /path/to/file.sql`" > result.txt

answered Jul 17, 2018 at 13:55

Rjazhenka

1,5181 gold badge11 silver badges6 bronze badges

1 Comment

Pritam Banerjee Over a year ago

Please add some explanation.

Collectives™ on Stack Overflow

How can I execute an SQL file, and save the results to a txt file in one command?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related