CODEWITHSUNDEEP
sqlsql-serverimportexportetl
kenwarner
kenwarner

Reputation: 29120

How to export text data from a SQL Server table?

I am trying to use the MS SQL Server 2005 Import/Export tool to export a table so I can import it into another database for archival. One of the columns is text so if I export as comma-delimited, when I try to import it into the archive table, it doesn't work correctly for rows with commas in that field. What options should I choose to ensure my import will work correctly?

Upvotes: 1

Views: 14556

Answers (3)

kenwarner
kenwarner

Reputation: 29120

Over a year later, I now have an ideal solution to my data export needs, thanks to https://stackoverflow.com/questions/20363/

bcp "SELECT * FROM CustomerTable" queryout "c:\temp\CustomerTable.bcp" -N -S SOURCESERVERNAME -T 

bcp TargetDatabaseTable in "c:\temp\CustomerTable.bcp" -N -S TARGETSERVERNAME -T -E
  • -N use native types
  • -T use the trusted connection
  • -S ServerName
  • -E Keep identity values specified in the data file

Very quick and easy to embed within code.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96600

I never use the comma delimter unless the client requires it. Try using | as a delimter. YOu can also use the text qualifier if need be.

Upvotes: 2

Abel Gaxiola
Abel Gaxiola

Reputation: 402

Use quotes as text qualifier

Text qualifier: Type the text qualifier to use. For example, you can specify that each text column be surrounded with quotation marks.

Upvotes: 0

Related Questions