Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unanswered: Export data from 2 tables to a text file.

    How do I export data from more than one table into a text file ?

    Can I also export only certain fields in the table to the text file and have required format in the text file ?

    Thanks & Regards,
    SJ

  2. #2
    Join Date
    Sep 2003
    Posts
    66
    can "select * from table1,table2;" ?

  3. #3
    Join Date
    Jun 2003
    Posts
    7
    Hi,
    No this alone will not be enough to export data to a text file, after searching thru the web i got this

    SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY "\n"
    FROM test_table;

    but when i run this query it gives an error :-

    "Incorrect syntax near '/tmp/result.text'."
    maybe there is some problem with the path..... can someone help me on this ??

  4. #4
    Join Date
    Sep 2003
    Posts
    66
    some one ask can "select into..." before, seems no answer

  5. #5
    Join Date
    Jun 2003
    Posts
    7
    I am still stuck with exporting data to a text file. I found some query, but I am having a problem in executing them in SQL query analyser

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors where state="'CA'" queryout authors.txt -U sa -P pwd -c'

    when i run this in SQL query analyzer is it gives an err :-

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'CA'.

    I've tried all kinds of combination like storing the string in a temp variable & then trying to run it, but it doesn't work....

    ------------------------------------------------------------------------------------
    From my local SQL server, when I try to run from the command prompt it works & the result is like this:-

    C:\>bcp "SELECT * FROM pubs..authors where state='"CA"'" queryout authors.txt -U
    sa -P quest -c

    Starting copy...

    15 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 30


    But the problem is I cannot run this command for my remote SQL server machine, I have to execute the command from SQL query analyzer only

    ------------------------------------------------------------------------------------

    There must be some problem in string concatenation. Someone pl. help me....

    Thanks
    SJ

  6. #6
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by sj_rao74
    I am still stuck with exporting data to a text file. I found some query, but I am having a problem in executing them in SQL query analyser

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors where state="'CA'" queryout authors.txt -U sa -P pwd -c'

    when i run this in SQL query analyzer is it gives an err :-

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'CA'.

    I've tried all kinds of combination like storing the string in a temp variable & then trying to run it, but it doesn't work....

    ------------------------------------------------------------------------------------
    From my local SQL server, when I try to run from the command prompt it works & the result is like this:-

    C:\>bcp "SELECT * FROM pubs..authors where state='"CA"'" queryout authors.txt -U
    sa -P quest -c

    Starting copy...

    15 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 30


    But the problem is I cannot run this command for my remote SQL server machine, I have to execute the command from SQL query analyzer only

    ------------------------------------------------------------------------------------

    There must be some problem in string concatenation. Someone pl. help me....

    Thanks
    SJ

    Try this:

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors where state=''CA'' queryout authors.txt -U sa -P pwd -c'

    before and after CA two simple quotes...

  7. #7
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by ioana
    Try this:

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors where state=''CA'' queryout authors.txt -U sa -P pwd -c'

    before and after CA two simple quotes...

    Sorry I forgot the last double quotes to close the select statement.
    So, try this:
    EXEC master..xp_cmdshell 'bcp "SELECT * FROM pubs..authors where state=''CA'' " queryout authors.txt -U sa -P pwd -c'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •