Error while inserting - String or binary data would be truncated

Navigation
3

When doing this insert: 

INSERT INTO t_cmo_oit1980_leitura(id_oit_let,id_oit,rx_num, dt_rx)
SELECT (1),
  (SELECT id_oit
   FROM t_cmo_oit1980
   WHERE id_oit = 6574),
  (SELECT rx_num
   FROM t_cmo_planilha_leitura
   WHERE id_xfc = 39517),
  (SELECT dt_rx
   FROM t_cmo_planilha_leitura
   WHERE id_xfc = 39517);

GO

The error happens:

  

Message 8152, Level 16, State 14, Line 19 String or binary data   would be truncated. The statement has been terminated.

Table t_cmo_planilha_leitura(Origem)

Tablet_cmo_oit1980_leitura(Destino)

    
asked by anonymous 19.04.2017 / 12:55

2 answers

4

The problem is actually in the RX_NUM field.

In the t_cmo_planilha_leitura table it is of type varchar(255)
And in the t_cmo_oit1980_leitura table it is of type varchar(6)

That is, they are of different sizes and the error is caused because the Destination does not have enough space to store what comes from the Source .

Correction Tips

1.  The best would be to change to varchar(255) , however, you can change to the maximum size.

Verify the maximum space used in the source: 

select max(len(rx_num)) from t_cmo_planilha_leitura

Knowing the maximum size, change in the target table: 

alter table t_cmo_planilha_leitura alter column rx_num varchar(o_valor_max)

2. Or you can directly limit what will be inserted into the target table by using the left command: 

insert into t_cmo_oit1980_leitura(id_oit_let,id_oit,rx_num, dt_rx)  
    select
    (1),
    (select id_oit from t_cmo_oit1980 where id_oit = 6574),
    (select left(rx_num,6) from t_cmo_planilha_leitura where id_xfc = 39517),
    (select dt_rx from t_cmo_planilha_leitura where id_xfc = 39517);
    go
    
19.04.2017 / 13:51
2

You are trying to add a larger value than the column allows.

RX_NUM in t_cmo_oit1980_leitura ( table receiving data ) has a length of varchar(6) , and RX_NUM in t_cmo_planilha_leitura has length varchar(255) .

We assume you have this text added as RX_NUM to t_cmo_planilha_leitura :

  

Microsoft SQL Server. database management system   relational (DBMS). Microsoft SQL Server is a DBMS - system   relational database manager developed by Microsoft.   It was created in partnership with Sybase in 1988 initially for the   OS / 2 platform.

And trying to add this text to t_cmo_oit1980_leitura will cause the error that you posted.

    
19.04.2017 / 13:40
Cloud of words in R Sort Select by ID javascript