SQL Get Delimited Results for All Columns

Question

I'm working on a simple SQL statement to generate BCP files to be loaded into the database.

These BCP files are in the following format:

1|name|otherfield|otherfield1

To build files like this I'm currently doing:

SELECT id+"|"+name+"|"+otherfield+"|"+otherfield1+"\n" FROM table

Is there a select statement that will select every column without having to name them?

Something like

SELECT * with "|" from Table

Answer 1

You could use SELECT ... INTO OUTFILE.

SELECT * INTO OUTFILE 'filename'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
FROM yourtable;

http://dev.mysql.com/doc/refman/5.6/en/select-into.html

Answer 2

If you want the output in a file then you can try this

SELECT *
INTO OUTFILE 'D://abc.txt'
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
FROM table

here I have used \r\n for outputting each row on a new line (ON windows)