Exporting Data to Text File with Text qualifier (double quotes)



--Exporting Data with Text qualifier (double quotes)

declare @qrytext varchar(1000), @filepath varchar(1000)

--Step 1
--Add a double quote to the beginning of each row by adding char(34) to the first column
set @qrytext = '"select   Char(34)+[ACCT #] as  [ACCT #],[SERV AGENT],[CROP],[HOMEOWNERS],[LIFE] FROM [test1].[dbo].[mytest888] "' 
---Step 2 define the data file
set @filepath = ' "C:\temp\myData1.txt" '

--Step 3 Call bcp
DECLARE @cmd NVARCHAR(4000) 
set @cmd = 'BCP ' + @qrytext + ' QUERYOUT ' + @filepath + '  -c -T -t\",\"  -S'+ @@servername
exec master..xp_cmdshell @cmd



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s