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,
SJcan "select * from table1,table2;" ?|||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 ??|||some one ask can "select into..." before, seems no answer ;)|||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|||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...|||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'sql