Bulk insert from txt file in sql server

• chatwithkrishan

  Ten Centuries

  Points: 1056

  More actions

  July 30, 2009 at 10:11 am

  #135034

  I have flat file value like this....

  1,"James","Smith",19750101

  2,"Meggie","Smith",19790122

  3,"Robert","Smith",20071101

  4,"Alex","Smith",20040202

  when i do bulk import by the query

  BULK INSERT TEMP_B1 FROM

  'D:\cmd.txt'

  WITH (FIELDTERMINATOR = ',',FirstRow=2,ROWTERMINATOR = '')

  how can replace the double quotes(") by empty string in this query

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  July 30, 2009 at 11:07 am

  #1032543

  Unfortunately, there's no easy way to do it as BULK INSERT has no TEXTTERMINATOR option. ("Why not?" is one of the great mysteries in the history of SQL Server.)

  What most people do is to import files into "Staging" tables where they can cleanse the data before it goes into the actual production tables. It does slow down the import process, but it gives you much greater control and options.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]