TSQL error while inserting "String or binary data will be truncated"

Question

In the code below, I insert the values ​​into the table and get the error "String or binary data will 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

Paste the code (I declared the variables constant values ​​for testing, I took these values ​​from what they were at run time):

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 separately, it seems that they are all in the length limits of a table variable. Any idea why I am getting this error?

Thank!

Answer 1

Problem with your insert request - INSERT PROCEDURE:

SELECT fd_ctl_nbr = @cur_fd_ctl_nbr,

INSERT , create script.

:

   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

. , SELECT INSERT , .

→ http://sqlfiddle.com/#!3/0e09b/1

, !!!

Answer 2

insert . , , , .

, , ( , , ) , , .

, , char , . char, , feb30, 2014. , . char , (, 2- ), . , , . , "VA" - , "VA". , , 1% Char.

Answer 3

, . :

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)

: .