Search code examples
sqlsql-serverxmlbcp

BCP SQL Server: How to insert double quoted at the first Column

In BCP, When I Export Data from Table i have data

1","Engineering","Research and Development","2002-06-01 00:00:00.000"
2","Tool Design","Research and Development","2002-06-01 00:00:00.000"
3","Sales","Sales and Marketing","2002-06-01 00:00:00.000"

But I want to export with double-quoted in the first Column. Like this:

"1","Engineering","Research and Development","2002-06-01 00:00:00.000"
"2","Tool Design","Research and Development","2002-06-01 00:00:00.000"
"3","Sales","Sales and Marketing","2002-06-01 00:00:00.000"

This is my format file:

    <?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='\",\"' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='\"\r\n' MAX_LENGTH="10"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>
Solution

  • mey be this could help:

    bcp "SELECT '\"'+Field1,Field2,Field3,Field4 from DB.dbo.Table" queryout C:\temp\data.txt -t\",\" -r\"\r\n -S -T -c