3
set identity_insert tblindividual ON

insert into tblIndividual (nametitle, individ, lastname, firstname, occupation, employer, active, editor, creationdate)
   select 
       salutation, idnumber, last_name, first_name, occupation, 
       employer, 1, recstatus, GETDATE()
   from 
       i_master 
   where 
       cc_rectype = '1' 

set identity_insert tblindividual OFF

Receiving error:

Msg 8152, Level 16, State 2, Line 2
String or binary data would be truncated. The statement has been terminated.

Rudi Kershaw
  • 10,659
  • 6
  • 46
  • 72
TrialByError
  • 53
  • 1
  • 6
  • 1
    As the error clearly says: trying to insert this set of data will **truncate** something - at least one of your **columns** in the table is defined **too short** to handle the data you're trying to insert – marc_s Feb 21 '15 at 21:09
  • 2
    And no, there's no way to find out which column is actually the problem. Go vote here to ask Microsoft to fix this: https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name it is one of the most annoying error messages in SQL Server – Icarus Feb 21 '15 at 21:13

1 Answers1

4

You're getting that error because one of the columns you are trying to insert data into is receiving data that is too large and would have to be truncated (cut short).

For example, if your nametitle field is a varchar(50) data type, but your salutation data was of type varchar(100) there could be values that will not fit without being truncated.

If you are happy to let your longer data be cut to size you can turn off the warning to allow the insert to continue using the statement: 

SET ANSI_WARNINGS  OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;

As above, always remember to turn warnings back on again afterwards. I hope this helps.

Rudi Kershaw
  • 10,659
  • 6
  • 46
  • 72
  • @TrialByError - No problem, glad I could help. If your problem has been solved don't forget to accept an answer by clicking the grey tick next to the answer. – Rudi Kershaw Mar 31 '15 at 10:20