TSQL error on insert "String or binary data would be truncated"

Question

In the code below I am inserting values into a table and getting the error "String or binary data would be truncated."

My table definition:

CREATE TABLE urs_prem_feed_out_control
( 
bd_pr_cntl_rec_type  char(7)  NULL ,
pd_pr_cntl_acctg_dte char(6)  NULL ,
bd_pr_cntl_run_dte   char(10)  NULL ,
bd_pr_cntl_start_dte char(10)  NULL ,
bd_pr_cntl_end_dte   char(10)  NULL ,
bd_pr_cntl_rec_count char(16)  NULL ,
bd_pr_tot_premium    char(16)  NULL ,
bd_pr_tot_commission char(16)  NULL ,
fd_ctl_nbr           integer  NOT NULL 
)

DECLARE @cur_fd_ctl_nbr INT = 2, 
@acctg_cyc_ym_2 CHAR(6) = '201402',
@rundate CHAR (10) = CONVERT(CHAR(10),GETDATE(),101),
@cycle_start_dt DATETIME = '2014-02-17',
@cycle_end_dt DATETIME = '2014-02-24',
@record_count INT = 24704,
@tot_pr_premium DECIMAL(18,2) = 476922242,
@tot_pr_comm DECIMAL(18,2) = 2624209257

Insert code (I've declared the variables as constant values for testing, I took these values from what they were at runtime):

INSERT INTO urs_prem_feed_out_control
SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,
       bd_pr_cntl_rec_type      = 'CONTROL',
       bd_pr_cntl_acctg_dte     = @acctg_cyc_ym_2,
       bd_pr_cntl_run_dte       = @rundate,
       bd_pr_cntl_start_dte     = CONVERT(CHAR(10),@cycle_start_dt,101),    
       bd_pr_cntl_end_dte       = CONVERT(CHAR(10),@cycle_end_dt,101),   
       bd_pr_cntl_rec_count     = RIGHT('0000000000000000' +     RTRIM(CONVERT(CHAR(16),@record_count)),16),                                         
       bd_pr_tot_premium        = CASE       
                                     WHEN @tot_pr_premium < 0
                                        THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
                                     ELSE
                                        '+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
                                     END,                                        
        bd_pr_tot_commission    = CASE       
                                     WHEN @tot_pr_comm < 0
                                        THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
                                     ELSE
                                        '+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
                                     END

When I look at each value individually it seems like they are all within the variable length constraints of the table. Any idea why I'm getting this error?

Thanks!

Solution 1

The problem with your insert query is THE ORDER OF INSERTION :

SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,

This column must be defined at the last in the INSERT as its the last column defined in the create table script.

Change your query to this:

   INSERT INTO #urs_prem_feed_out_control (fd_ctl_nbr, bd_pr_cntl_rec_type, pd_pr_cntl_acctg_dte, bd_pr_cntl_run_dte, bd_pr_cntl_start_dte, bd_pr_cntl_end_dte, bd_pr_cntl_rec_count, bd_pr_tot_premium, bd_pr_tot_commission)
SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,
       bd_pr_cntl_rec_type      = 'CONTROL',
       bd_pr_cntl_acctg_dte     = @acctg_cyc_ym_2,
       bd_pr_cntl_run_dte       = @rundate,
       bd_pr_cntl_start_dte     = CONVERT(CHAR(10),@cycle_start_dt,101),    
       bd_pr_cntl_end_dte       = CONVERT(CHAR(10),@cycle_end_dt,101),   
       bd_pr_cntl_rec_count     = RIGHT('0000000000000000' +     RTRIM(CONVERT(CHAR(16),@record_count)),16),                                         
       bd_pr_tot_premium        = CASE       
                                     WHEN @tot_pr_premium < 0
                                        THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
                                     ELSE
                                        '+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_premium)*100))),18),1,15)
                                     END,                                        
        bd_pr_tot_commission    = CASE       
                                     WHEN @tot_pr_comm < 0
                                        THEN '-' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
                                     ELSE
                                        '+' + SUBSTRING(RIGHT('000000000000000' + LTRIM(RTRIM(CONVERT(VARCHAR,ABS(@tot_pr_comm)*100))),18),1,15)
                                     END

Doing this would also work. Notice that the first column here in the SELECT within the INSERT is just the way you have provided in your question.

See this here-> http://sqlfiddle.com/#!3/0e09b/1

Hope this helps!!!

Solution 2

This is why you should never write an insert statement without specifying the columns. Since you did not, it wil try to put the data in the columns in the order they are in the table which is not at all the order you have them in.

Another thing that can happen when you get this sort of message (but which I don't think applies in your case, I include it for people searching later) is that the eeror is actually coming from a trigger and not the main insert.

Finally a note on database design, you should not be using char for dates, you should be using date fields. You cannot do date math on a char field and it will accept incorrect date values like feb30, 2014. It is always a bad idea to store dates as anything except date or datetime values. In general char should only be used rarely when a column will always have the same number of characters (like a 2 column state abbreviation), it should not be used as the default datatype. You need to do a better job of defining datatypes that match the type and size of data being stored. You can run into problems with queries as 'VA' is not the same thing as 'VA '. In general my experience is that less than 1 % of all database fields should be Char.

Solution 3

I think it may be beneficial to double check your work. Consider the following example code:

DECLARE @Table TABLE (Name NVARCHAR(1))

INSERT INTO @Table (Name) SELECT 'ab'
INSERT INTO @Table (Name) SELECT SUBSTRING('ab',1,2)
INSERT INTO @Table (Name) SELECT RIGHT('abc',2)

All yield the following: String or binary data would be truncated.