3

I am trying to do a bulk insert into a SQL table and I keep getting the error Bulk load data conversion error (truncation) for row when I run the stored procedure. I have included a row and field terminator. Any ideas what could be wrong here? TY

The source file has tabs between each field.

ALTER  PROCEDURE [dbo].[sp_app_Import] 

AS
DECLARE @SQL varchar(2000)
/*Truncate temporary table*/
TRUNCATE TABLE tbltableTemp

BULK INSERT tbltableTemp
FROM '\\server\public\Source_Files\test.txt' 
WITH (FIELDTERMINATOR='\t', ROWTERMINATOR = '\r', FIRSTROW = 2)
/*Truncate permanent table*/
TRUNCATE TABLE tbl_test


INSERT INTO tbl_test
(MemberID,FacilityName,FacilityAddress1,FacilityAddress2,FacilityCity,FacilityState,FacilityZip,HIN,GLN,LIC,GPO)

SELECT '' AS MembershipID,RawData6 as FacilityName, RawData7 as FacilityAddress1,RawData8 as FacilityAddress2,RawData9 as FacilityCity, RawData10 as FacilityState, 
RawData11 as FacilityZip, RawData26 as HIN, RawData2 as GLN, RawData1 as LIC, 'test' as GPO 

FROM         tbltableTemp
Improve this question
6
  • 1
    Sounds like your data is being truncated.
    – Jacob H
    Commented Jul 14, 2017 at 18:22
  • Don't the row terminator values fix these truncation issues? Not sure what other options I have
    – user1342164
    Commented Jul 14, 2017 at 18:26
  • 1
    Your field terminator is a tab. That denotes the end of the field and beginning of the next field. If you have a column with 10 characters and you are inserting into a column that is 2 characters, how does the tab fix the length of the data?
    – Jacob H
    Commented Jul 14, 2017 at 18:30
  • Try setting ANSI WARNINGS OFF and run your query. It will probably insert--because SQL isn't warning you about the data truncation anymore. But now you have partial data in one or more of your columns. The point is there is most likely a problem with your data. Not the query.
    – Jacob H
    Commented Jul 14, 2017 at 18:34
  • I have it working now, destination column was too small. Thank you for your help.
    – user1342164
    Commented Jul 14, 2017 at 18:42
Related questions
21
Bulk load data conversion error (truncation)
2
SQL Server bulk insert - "Bulk load data conversion error"
1
Bulk load data conversion error (truncation) - SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.