July 18, 2008 at 7:50 am
Hi,
I am having trouble trying to do bulk insert into my table.
I know my data file has some records which is longer than the length of the columns in the table. I need the data to be truncated and inserted into the table without producing an error.
In SQL 2000 we accomplished this by using SET ANSI_WARNINGS OFF.
In SQL 2005, I have done this but it is still giving the errors. I have also turned off ARITHABORT
my table looks like this:
create table test(
firstname varchar(5),
lastname varchar(5))
My data(tab delimited) looks like this:
abcdefghabcdefgh
ijklmnopijklmnop
qrstuvwxqrstuvwx
I am using a format file with the bulk insert which is like this:
8.0
2
1 SQLCHAR 0 5 "\t" 1 fname ""
2 SQLCHAR 0 5 "\r" 2 lname ""
I have also tried to change the 8.0 to a 9.0
I have even tried SET ANSI_DEFAULTS OFF.
I have even tried BCP but the same thing happens.
I want the data to be truncated and loaded into the table without any errors
this is the kind of error I am getting:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 944, column 96 (PR_DESC).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1074, column 96 (PR_DESC).
Msg 4865, Level 16, State 1, Line 1
Please help!!!
July 18, 2008 at 1:19 pm
My usual solution would be to either set up an SSIS package and have it clean up the data in there before it goes into the final table, or to set up a staging table, and clean up from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2008 at 3:07 pm
staging stable is not going to work as the length of the data is unknown. it may be 10 characters or 10,000 characters.
I would assume the same restrictions would apply on ssis.
Can you please provide more information on SSIS method of you can, as I am new to this.
Feel free to use my test table and the data as above. Again the length of the data in the file is unknown.
July 19, 2008 at 9:17 am
SQL_DBA (7/18/2008)
staging stable is not going to work as the length of the data is unknown. it may be 10 characters or 10,000 characters.
A Staging table will work. Just define the column as VARCHAR(MAX) in the staging table.
[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]
July 21, 2008 at 8:51 am
I got it to work.
No staging table needed.
The trick was to put a very large max-Lenght in the fmt file and set ansi_warnings off.
This worked.
Thanks for all suggestions!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply