Bcp data with quotes

I have a table with the following data
A_Id A_Name A_Desig
A324 Author1 Script Writer
T533 Tester Test cases
Now I want to export the data to text file with the following format
"A324" "Author1" "Script Writer"
"T533" "Tester" "Test cases"
Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
varchar(100)
set @.table='Author'
set @.FileName='C:\Author.txt'
set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
'+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
Exec(@.str)
But I get the result with first and last quotes missing
A324" "Author1" "Script Writer
T533" "Tester" "Test cases
How do I get the desired result?
MadhivananHi Madhivanan
You have a couple of choices. You could either specify a format file or add
the additional "" to your select statement. I've created a quick example
against the pubs database table employee for you ...
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -t, -Ssql-test -T
This result in...
"A-C71970F","Aria"
"A-R89858F","Annette"
"AMD15433F","Ann"
"ARD36773F","Anabela"
"CFH28514M","Carlos"
"CGS88322F","Carine"
"DBT39435M","Daniel"
"DWR65030M","Diego"
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138178304.808428.247350@.f14g2000cwb.googlegroups.com...
>I have a table with the following data
> A_Id A_Name A_Desig
> A324 Author1 Script Writer
> T533 Tester Test cases
> Now I want to export the data to text file with the following format
> "A324" "Author1" "Script Writer"
> "T533" "Tester" "Test cases"
> Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
> varchar(100)
> set @.table='Author'
> set @.FileName='C:\Author.txt'
> set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
> '+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
> Exec(@.str)
> But I get the result with first and last quotes missing
> A324" "Author1" "Script Writer
> T533" "Tester" "Test cases
>
> How do I get the desired result?
> Madhivanan
>|||Thanks, Ryan
When I run your query, I get yhis error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+emp_id+'.
Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Thanks, Ryan
> When I run your query, I get yhis error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '+emp_id+'.
A query? Ryan did not post a query. He posted something run in a command-
line window. (I tested it, and it works, once I had corrected the
table name, and removed the -S option to run on the local server.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland
I also made some changes to work
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -q
Madhivanan