Export query to text file

What I am trying to do is export a Tsql query to a csv file. Simple enough, however I need to be able to specify which fields are wrapped in quotes "开发者_如何转开发". I can get my query to export with all the feilds wrapped.

"SCHEN001","Joe Bloggs Inc","1","1","1","1","1","1","13","6","Mr John Smith"

What I would like to export is

"SCHEN001","Joe Bloggs Inc",1,1,1,1,1,1,13,6,"Mr John Smith"

Is this possible using Tsql?

Any ideas would be greatly appreciated

---

Take a look at the bcp.exe utility. It is ment for bulk operations and you can specify templates for exports etc.

A link that seems reasonable: http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

---

Another approach is to use SQL Server Integration Services (if you have MS SQL Server Standard or Enterprise edition)

or, alternatively, you can copy grid results into Excel, and export CSV from there :-)

---

Try to use this script.

Set variable @TblName to the name of your table.

The script uses information_schema.columns to get the datatypes for every column in selected table.

DECLARE @TblName varchar(128)
DECLARE @WhereClause varchar(255)

DECLARE @cmd varchar(7000)
SET @TblName = '<YOUR TABLENAME>' --TABLENAME
SET @cmd = ''

create table #tableDef (id int identity (1,1), ColType int, ColName varchar(128))

--Fetch table information
insert  #tableDef (ColType, ColName)
select case when DATA_TYPE like '%char%' then 1
            when DATA_TYPE like '%datetime%' then 2 
            else 0 end ,
    COLUMN_NAME
from    information_schema.columns
where   TABLE_NAME = @TblName
order by ORDINAL_POSITION

SELECT   @cmd = @cmd
                + ' CASE WHEN ' + ColName + ' IS NULL '
                +   ' THEN ''NULL'' '
                +   ' ELSE '
                +     case ColType 
                      when  1 then  ''''''''' + ' + ColName + ' + ''''''''' 
                      when  2 then  ''''''''' + ' + 'CONVERT(VARCHAR(20),' + ColName + ')' + ' + '''''''''                    
                      else 'CONVERT(VARCHAR(20),' + ColName + ')' end
                + ' END + '','' + '
        from    #tableDef
order by id

select @cmd = 'SELECT  ' + left(@cmd,len(@cmd)-8) + '+'''' FROM ' + @tblName 

exec (@cmd)

drop table #tableDef