SQL Command to copy the result to a file

Being more Concerned about the queries and saving their results in sql i found out a way to save the results directly into a file through the command in desired format.

Here it goes:

mysql> select emp_id,emp_name from emps

into outfile ‘c:/test.txt’;

This simply produces the file without any fuss,each column output as in the table.

We can also manipulate the file by simply formatting the output of the sql,so if we wanted to output each column in the table with a comma delimeter we would have to concatenate the values together in the SQL to produce the required result.In mysql there are number of options we can use with INTO OUTFILE to change the  way data is written to the file.These are FIELDS ESCAPED BY,FIELDS ENCLOSED BY,FIELDS TERMINATED BY.So if we wanted our output to be enclosed in double quotes,with comma delimiters and a new line termination for eac row we would use.

Select emp_id,emp_name

INTO OUTFILE  ‘c:/result.text’

FIELDS TERMINATED BY ‘,’   ENCLOSED BY  ‘  ”  ‘

LINES TERMINATED BY  ‘ \n ‘

FROM emps;

That is it.....
Hope it Helps :)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: