November 1st, 2012, 12:03 PM
Select columns from tables and place in outile
Ok so if I want to select data from a specific table and only specific columns and then put that data into a outfile would my syntax be the following:
into outfile '/tmp/filename.txt';
That would capture said data for 4 columns 1,2,5,8 from the table called Specific_Tbl putting the data into a file so called (what every I call it and located at the path I give)? Correct? have I left anything out?
Also if I wanted to select all columns from a table I would do the following and put it into a outfile:
into outfile '/tmp/filename.txt';
Is that also correct?
November 1st, 2012, 12:45 PM
relative to your server root, but yeah, looks okay
have you tested these queries yet?
November 1st, 2012, 04:24 PM
Yes I did run the syntax and yes I have another issue it would appear.
I guess I need to set the output somehow to list the column's names at the top line for each select, and then to use the option of FIELDS TERMINATED by '|'
how to do that I need to play with a little more...
November 2nd, 2012, 10:07 AM
Ok the syntax to get the results for each of the tables I needed to is the following:
SELECT * FROM FundCrossReference
into outfile '/home/ (company_name)/CUSTDBEXTRACTS/FundCrossReferenceDMEXTRACT.txt'
fields terminated by '|' ;
This works but now I am trying to get the column_names to list at the top of these files...
I tried using the --column-names option on the mysql login like:
mysql -h localhost -u root -p --column-names (db_name)
but when VI the file the columns are not listed.
How might I get the column names?
November 2nd, 2012, 10:15 AM
In Linux/Unix/Mac OSX you would run this from the shell. In Windows environment you can run it from your COMMAND prompt.
Make sure to keep everything before the pipe on one line. Your query can be as complicated as it needs to be.
of course you replace SELECT * FROM tablename with your actual query.
echo SELECT * FROM tablename | mysql -uusername -ppassword databasename > file.txt
November 2nd, 2012, 01:31 PM
I get a error when I run it...
./DataMartExtract.ksh: syntax error at line 8 : `|' unexpected
We are a Solaris 10 shop
within my #!/bin/ksh
my syntax is...
echo SELECT * FROM FundCrossReference into outfile '/home/qedadm1/CUSTDBEXTRACTS/FundCrossReferenceDMEXTRACT.txt' fields terminated by '|' ; | mysql -h localhost -u root -p --column-name Q2DataMart
what should it be it seems to balk at the '|' needed for fields terminated by '|' what should or how should I wrie this?
November 2nd, 2012, 03:27 PM
Ok I figured it out...put it all into a ksh file and ran it all as a function for the 28 tables and checked the data...
#Extract of customer specific data from the database
rm -f $outfile
echo "SELECT $columns FROM $table LIMIT 1" | mysql -h localhost -u root --password=(pwd_not_shown) (DB_Name) | sed 1q | sed 's/ */|/g' >/tmp/f1
echo "SELECT $columns FROM $table into outfile '/tmp/f2' fields terminated by '|' ;" | mysql -h localhost -u root --password=(not_shown) (DB_Name)
cat /tmp/f1 /tmp/f2 >$outfile
rm -f /tmp/f1 /tmp/f2
dump_table FundCrossReference \* /home/dir_path/CUSTDBEXTRACTS/FundCrossReferenceDMEXTRACT.txt
<snip followed by 27 others tables>
Last edited by ByGoneYrs; November 2nd, 2012 at 03:32 PM.
November 5th, 2012, 02:04 PM
BTW just as a note, it is best to NOT have the two temp files created in the /tmp directory, but instead have them created in the same directory as the outfile's themselves are being created. In solaris 10 if your using Zones and you use up all the space in tmp you will cause your process to die and other processes to also die too. I had that happen, but on my 2nd try I was able to get it all to work correctly.