Copy records from one table to another via select

Question

Copy records from one table to another via select

Navigation

#1 by (3 votes)

1

I am copying the records from one table to another on different banks, however a column in the new table can not be null and the data coming from the old table is null. Is there a way to put some conditional so that it "turns" null fields into something I want?

Below the query I'm using

insert into banco_novo.cliente (codcliente, nome, cpf, rg, sexo, data_nascimento, endereco, bairro, cidade, estado, cep) (select codcliente, nome, cpf, rg, sexo, data_nascimento, endereco, bairro, cidade, estado, cep from banco_antigo.cliente)

mysql sql

asked by anonymous 06.08.2018 / 20:29

1 answer

Random in the switch statement, why does not it work? Put error message in the view Laravel 5.6

score 3 · Accepted Answer

There is the function IF () with it you you can "transform" the null values to some other value in your insert... select , see this example:

insert into table2(nome)
select if (nome is null,'padrao para nulo', nome)
from table1;

The values you saw as nulls in the nome field are now contained in the 'padrao para nulo' value.

See the code working in SQL Fiddle .