July 30, 2009 at 10:11 am
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
July 30, 2009 at 11:07 am
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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply