#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102

    Select columns from tables and place in outile


    Hello everyone,

    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:

    select
    column1,
    column2,
    column5,
    column8
    from (Specific_Tbl)
    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:

    select *
    from (Specific_Tbl)
    into outfile '/tmp/filename.txt';


    Is that also correct?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    relative to your server root, but yeah, looks okay

    have you tested these queries yet?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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...
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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?
  8. #5
  9. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    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.


    Code:
    echo SELECT * FROM tablename | mysql -uusername -ppassword databasename > file.txt
    of course you replace SELECT * FROM tablename with your actual query.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    I get a error when I run it...

    ./DataMartExtract.ksh
    ./DataMartExtract.ksh[8]: 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?
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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...

    #!/bin/ksh
    #
    #Extract of customer specific data from the database
    #
    #

    dump_table()
    {
    table=$1
    columns=$2
    outfile=$3

    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 02:32 PM.
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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.

IMN logo majestic logo threadwatch logo seochat tools logo