Error doing Insert - String or Binary data > would be truncated

Asked

Viewed 43,536 times

6

By making this Internet:

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 mistake happens:

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

Table t_cmo_planilha_leitura(Origem)

inserir a descrição da imagem aqui

Table t_cmo_oit1980_leitura(Destino)

inserir a descrição da imagem aqui

sql sql-server sql-server-2014

  • It may not be related to the data field. The message says "String or binary data would be truncated". You can detail the data types of each target field and source of that query?

  • @Pagotti, I will edit the post and put the structure of each table.

  • @Pagotti, you’re right. I mistook Data for Date. I already know what the mistake is. The Origin table comes from an excel spreadsheet and when creating by DTS, it mounts the varchars with 255 and in the target field is varchar(6) and this for the other fields. I will recreate the table and click again. That’s it, change your comment in response so I mark it.

  • @pnet I edited your question to make it more comprehensive. :)

2 answers

7


The problem is actually in the field RX_NUM.

On the table t_cmo_planilha_leitura he’s the type varchar(255)
And on the table t_cmo_oit1980_leitura he’s the type varchar(6)

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

Suggestions for correction

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

Check the maximum space used at 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 using the command left: 

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

2

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

RX_NUM in t_cmo_oit1980_leitura(table that receives the data) has a length varchar(6), and RX_NUM in t_cmo_planilha_leitura has a length varchar(255).

We assume you have this text added below there RX_NUM in t_cmo_planilha_leitura:

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

And try to add this text in t_cmo_oit1980_leitura, will occur the error you posted.

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.