CODEWITHSUNDEEP
sqlsybase
Pez Cuckow
Pez Cuckow

Reputation: 14422

SQL Get Delimited Results for All Columns

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

Upvotes: 5

Views: 1850

Answers (3)

Mike Gardner
Mike Gardner

Reputation: 6651

For Sybase ASE, why not just change the delemiter using the built in bcp utility. It will capture all the columns by default.

bcp MYDATABASE..MYTABLE out MYTABLEFILE.bcp -Uusername -Ppassword -SMYSERVER -c -t|

You have to use the -t option to set the column delimeter, which can only be used with the -c option specifying a 'character' (human readable) based bcp output. If you are on a Unix/Linux based system, you may have to escape the pipe, e.g. -\t

Upvotes: 1

Abubakkar
Abubakkar

Reputation: 15664

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)

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838786

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

Upvotes: 4

Related Questions