Bulk load data conversion error (truncation) ... I don't care if it truncates

Question

Bulk load data conversion error (truncation) ... I don't care if it truncates

J M-314995

SSCrazy

Points: 2975
More actions
May 15, 2014 at 11:18 am

#307952

Is there a switch I can use to force a bulk insert and if data is truncated, I'm good with that. The truncated data, in this case, is not data I can use anyway if it is long enough to be truncated.
I need to keep the field at VARCHAR(23) and if I expand it, I won't be able to join on it after the file load completes. I'd like the data to be inserted (truncated if need be) and then I'll deal with the records that are truncated after I load the file.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How are you bulk loading?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

J M-314995 SSCrazy Points: 2975 More actions · Answer 2

Right now I'm using SSMS but when I get everything put together, I'm going to throw the script into an SSIS package and put it on a schedule.

BULK INSERT dbo.daily_pmts

FROM 'E:\data\z.txt'

WITH

(

FIRSTROW = 2

,FIELDTERMINATOR = '\t'

,ROWTERMINATOR = ''

)

GO

By the way, I put a backslash and the letter n in the ROWTERMINATOR field but for some reason on this forum website, it keeps removing it.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

J M-314995 (5/15/2014)
Right now I'm using SSMS but when I get everything put together, I'm going to throw the script into an SSIS package and put it on a schedule.
BULK INSERT dbo.daily_pmts
FROM 'E:\data\z.txt'
WITH
(
FIRSTROW = 2
,FIELDTERMINATOR = '\t'
,ROWTERMINATOR = ''
)
GO
By the way, I put a backslash and the letter n in the ROWTERMINATOR field but for some reason on this forum website, it keeps removing it.

Lookup BULK INSERT in Books Online. There are switch settings to allow a very high number of errors and to actually capture bad "rows" in a separate file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

J M-314995 (5/15/2014)
Right now I'm using SSMS but when I get everything put together, I'm going to throw the script into an SSIS package and put it on a schedule.

If you're going to use SSIS anyway, you can just use a data flow task with a flat file source and an OLE DB destination.

Configure the source to redirect rows with truncation to a seperate file.

Or just follow Jeffs suggestion 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

J M-314995 SSCrazy Points: 2975 More actions · Answer 5

Well, most of the time BOL doesn't help me and I dislike most of the examples they give. My habit now days is to just open up google. The description on MAXERRORS seemed pretty straight forward though in BOL.

I think I'll use this switch instead of using SSIS. I'd prefer to use SQL to separate the errors out after the data has been imported on this specific task.

While searching around for a suggestion on this issue (using the search terms I used), I came across a lot of forum posts that suggested using FORMATFILE. The forum posts and BOL has given me a general idea on how it works but some of the forum posts (on other forums) confused me. Can I get a recommended detailed tutorial on FORMATFILE that gives good examples? I can use google to find a tutorial or two but if anyone has already been down this path and really took a liking to some specific documentation, I'd like to read it too. Reading the BOL documentation just leaves me with more questions about it.