SQL INSERT INTO SELECT

A Ana quer montar uma agenda de contatos da Loja Aurora, juntando os clientes e os fornecedores. A tabela da agenda já está criada, com as regras certinhas. Digitar 33 INSERT à mão, copiando nome por nome? Não: os dados já estão no banco. Com INSERT INTO … SELECT, o resultado de uma consulta vai direto para dentro de uma tabela que já existe. É o INSERT que você conhece, só que, no lugar do VALUES, entra um SELECT.

Você vai aprender

Como inserir numa tabela as linhas que um SELECT devolve.
Juntar dados de várias tabelas numa só, com valores fixos e calculados.
Por que as colunas casam pela posição, e não pelo nome.
Que as regras da tabela de destino (NOT NULL, UNIQUE, chaves) são conferidas, e o que acontece quando uma linha fura a regra.
Como não duplicar dados ao rodar duas vezes, com INSERT OR IGNORE.
Restaurar uma cópia de segurança, e a diferença para o CREATE TABLE … AS SELECT.

Esta aula junta duas: INSERT INTO e Criar Tabela a partir de Consulta. Os exemplos rodam de verdade no seu navegador, e cada Rodar começa do banco limpo.

A ideia: passar a limpo para um caderno pronto

Na aula anterior, a Ana tirava uma xerox: saía um caderno novo, com o texto e sem as regras. Agora é diferente: ela já tem um caderno de contatos pronto, com as linhas numeradas e o aviso "nome obrigatório" no topo. O que ela quer é passar a limpo para esse caderno as linhas certas dos outros cadernos.

	`CREATE TABLE nova AS SELECT …`	`INSERT INTO existente SELECT …`
A tabela de destino	É criada pelo comando	Já tem de existir
Regras (chave, `NOT NULL`, `UNIQUE`…)	Nenhuma	As que você escreveu no `CREATE TABLE`, e são conferidas
Pode rodar várias vezes	Não: na segunda, a tabela já existe	Sim: cada vez acrescenta linhas

1. O primeiro INSERT INTO SELECT

A agenda da Ana:

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente'
FROM clientes
WHERE email IS NOT NULL;

SELECT * FROM contatos LIMIT 5;

INSERT INTO contatos (nome, email, origem): Igual a um INSERT comum: a tabela de destino e a lista das colunas que vão receber valores. O id e o telefone ficaram de fora: o id se numera sozinho (aula AUTOINCREMENT) e o telefone fica NULL.
SELECT nome, email, 'cliente': No lugar do VALUES, uma consulta. Ela tem de devolver três colunas, uma para cada coluna da lista acima, na mesma ordem.
'cliente': Um valor fixo: um texto entre aspas simples, igual em todas as linhas. É assim que a Ana marca de onde veio cada contato.
FROM clientes WHERE email IS NOT NULL: Quais linhas copiar: só os clientes que têm email (aula IS NULL).

O quadro diz "27 linhas afetadas": dos 30 clientes, 3 não têm email (os clientes 7, 15 e 23). Repare que não há parênteses em volta do SELECT nem a palavra VALUES.

Mais uma fonte na mesma tabela

Como a tabela já existe, dá para rodar outro INSERT INTO … SELECT e acrescentar mais linhas. Agora os fornecedores, que têm telefone em vez de email:

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente'
FROM clientes
WHERE email IS NOT NULL;

INSERT INTO contatos (nome, telefone, origem)
SELECT nome, telefone, 'fornecedor'
FROM fornecedores;

SELECT origem, COUNT(*) AS contatos
FROM contatos
GROUP BY origem;

27 clientes e 6 fornecedores, 33 contatos numa tabela só. Os ids dos fornecedores continuaram de onde os clientes pararam: 28, 29…

Experimente: a tabela clientes_vip já está criada, mas vazia. Antes do SELECT do fim, copie para ela o id, o nome e a cidade dos clientes VIP (os que têm vip = 1).

CREATE TABLE clientes_vip (
    id     INTEGER PRIMARY KEY,
    nome   TEXT NOT NULL,
    cidade TEXT
);

SELECT * FROM clientes_vip;

CREATE TABLE clientes_vip (
    id     INTEGER PRIMARY KEY,
    nome   TEXT NOT NULL,
    cidade TEXT
);

INSERT INTO clientes_vip (id, nome, cidade)
SELECT id, nome, cidade
FROM clientes
WHERE vip = 1;

SELECT * FROM clientes_vip;

2. O SELECT pode fazer contas

Tudo que um SELECT sabe fazer vale aqui: contas, funções, JOIN, GROUP BY. A Ana vai montar a tabela de preços da promoção de volta às aulas: 10% de desconto em tudo da Papelaria (categoria 1):

CREATE TABLE precos_promocao (
    produto_id  INTEGER PRIMARY KEY REFERENCES produtos(id),
    preco_promo REAL NOT NULL
);

INSERT INTO precos_promocao (produto_id, preco_promo)
SELECT id, ROUND(preco * 0.9, 2)
FROM produtos
WHERE categoria_id = 1;

SELECT p.nome, p.preco, pr.preco_promo
FROM precos_promocao AS pr
JOIN produtos AS p ON p.id = pr.produto_id;

O ROUND(preco * 0.9, 2) calcula 90% do preço e arredonda para 2 casas (aula Funções Numéricas). A coluna calculada não precisa de apelido aqui, porque quem dá o nome é a lista de colunas do INSERT: preco_promo.

3. As colunas casam pela posição

Este é o detalhe mais importante da aula. O SQLite não olha os nomes das colunas do SELECT; ele põe a primeira coluna do SELECT na primeira coluna da lista, a segunda na segunda, e assim por diante. Veja o que acontece quando a Ana troca a ordem sem querer:

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT email, nome, 'cliente'
FROM clientes
WHERE email IS NOT NULL;

SELECT nome, email FROM contatos LIMIT 3;

Nenhum erro, 27 linhas gravadas, e todas erradas: o email foi parar em nome e o nome em email. O banco não tem como saber que "jose.ribamar@exemplo.com.br" não é um nome. Por isso:

Escreva sempre a lista de colunas no INSERT, e o SELECT na mesma ordem, uma embaixo da outra se ajudar.
Rode o SELECT sozinho antes, para ver se as colunas saem na ordem certa.
Depois do INSERT, confira algumas linhas.

E sem a lista de colunas?

Dá para escrever INSERT INTO tabela SELECT … sem a lista. Aí o SELECT tem de devolver todas as colunas da tabela, na ordem em que foram criadas, inclusive o id. Funciona bem para tabelas com a mesma estrutura (como uma cópia feita com CREATE TABLE … AS SELECT *), mas quebra no dia em que alguém acrescentar uma coluna (aula ALTER TABLE). Prefira a lista.

4. As regras do destino são conferidas

A grande vantagem sobre o CREATE TABLE … AS SELECT: a tabela de destino tem regras, e cada linha copiada passa por elas. Se uma linha furar uma regra, o comando inteiro é recusado e nenhuma linha entra.

NOT NULL

Numa agenda em que o email é obrigatório, a Ana esqueceu o WHERE:

CREATE TABLE emails (
    id    INTEGER PRIMARY KEY,
    nome  TEXT NOT NULL,
    email TEXT NOT NULL
);

INSERT INTO emails (nome, email)
SELECT nome, email
FROM clientes;

NOT NULL constraint failed: emails.email: os clientes 7, 15 e 23 não têm email. As outras 27 linhas também não entraram: ou vão todas, ou nenhuma. Com WHERE email IS NOT NULL, passa.

UNIQUE: rodou duas vezes

Um engano muito comum: rodar o mesmo INSERT INTO … SELECT duas vezes. Como a coluna email da agenda é UNIQUE, a segunda vez é barrada:

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

O primeiro INSERT gravou 27; o segundo deu UNIQUE constraint failed: contatos.email e não gravou nada. Sem o UNIQUE, a agenda teria ficado com 54 contatos, todos em dobro, sem aviso nenhum. Mais um motivo para pôr regras nas tabelas.

Pular as repetidas: INSERT OR IGNORE

Às vezes você quer rodar de novo, para trazer só o que for novo. O SQLite tem uma forma para isso: INSERT OR IGNORE ("insira ou ignore"). A linha que furaria uma regra de UNIQUE ou de chave primária é pulada, em silêncio, e as outras entram:

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

INSERT OR IGNORE INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

SELECT COUNT(*) AS contatos FROM contatos;

A segunda rodada: "0 linhas afetadas", e a agenda continua com 27. Se amanhã um cliente novo cadastrar o email, só ele entra. Use com consciência: o OR IGNORE também esconde problemas que você talvez quisesse ver.

5. Restaurar uma cópia de segurança

Na aula anterior, a Ana guardou uma cópia dos produtos antes de um reajuste. Imagine que o reajuste saiu errado: multiplicou todos os preços por 10. A cópia salva o dia:

-- a cópia de segurança, tirada antes
CREATE TABLE produtos_copia AS SELECT * FROM produtos;

-- o reajuste errado
UPDATE produtos SET preco = preco * 10;

-- a volta: cada produto da cópia substitui o de mesmo id
INSERT OR REPLACE INTO produtos
SELECT * FROM produtos_copia;

SELECT id, nome, preco FROM produtos LIMIT 3;

INSERT OR REPLACE: "Insira ou substitua": se já existe uma linha com a mesma chave primária (o mesmo id), ela é trocada pela nova.
SELECT * FROM produtos_copia: As 45 linhas da cópia, com todas as colunas na mesma ordem da tabela produtos (a cópia foi feita com SELECT *). Por isso, aqui, a falta da lista de colunas é segura.

Os preços voltaram: 12.35, 2.79, 1.15. Os itens de pedido continuam apontando para os mesmos ids, e as chaves estrangeiras não reclamaram. Para uma tabela que ninguém referencia, o caminho mais simples também funciona: DELETE FROM t; e depois INSERT INTO t SELECT * FROM t_copia;.

6. Formas de fazer

Forma	Escrita	Quando usar
Com lista de colunas	`INSERT INTO t (a, b) SELECT x, y FROM …;`	Sempre que der. A mais segura.
Sem lista	`INSERT INTO t SELECT * FROM t_copia;`	Tabelas com a mesma estrutura (cópia e original).
Pular repetidas	`INSERT OR IGNORE INTO t … SELECT …;`	Rodar de novo sem duplicar (precisa de `UNIQUE` ou chave).
Substituir as que já existem	`INSERT OR REPLACE INTO t … SELECT …;`	Restaurar uma cópia, atualizar uma tabela inteira de outra.
Um valor de consulta no meio do `VALUES`	`INSERT INTO t (a, b) VALUES ((SELECT x FROM … WHERE id = 3), 'fixo');`	Uma linha só, com um valor buscado de outra tabela.
Criar e encher de uma vez	`CREATE TABLE t AS SELECT …;`	Cópias e relatórios sem regras (aula anterior).

Quando dá errado

Número de colunas diferente (com lista)

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email
FROM clientes;

A mensagem: 2 values for 3 columns, "2 valores para 3 colunas". A lista pede três; o SELECT devolve duas. Faltou o 'cliente'.

Número de colunas diferente (sem lista)

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos
SELECT nome, email
FROM clientes;

A mensagem: table contatos has 5 columns but 2 values were supplied, "a tabela contatos tem 5 colunas, mas foram dados 2 valores". Sem lista, o SELECT precisa trazer as cinco.

Como consertar: escreva a lista de colunas: INSERT INTO contatos (nome, email, origem) SELECT nome, email, 'cliente' ….

VALUES junto com SELECT

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL
);

INSERT INTO contatos (nome, email, origem)
VALUES (SELECT nome, email, 'cliente' FROM clientes);

A mensagem: near "SELECT": syntax error. É um ou outro: VALUES (…) para linhas digitadas, ou SELECT para linhas de uma consulta.

Como consertar: apague o VALUES ( e o ) do fim.

Uma linha fura a regra: nenhuma entra

NOT NULL constraint failed: … ou UNIQUE constraint failed: … (seção 4). O comando inteiro é desfeito.

Como consertar: filtre com WHERE as linhas que não servem (WHERE email IS NOT NULL), ou, para repetidas, use INSERT OR IGNORE.

A tabela de destino não existe

INSERT INTO contato (nome)
SELECT nome FROM clientes;

A mensagem: no such table: contato. O INSERT INTO … SELECT não cria a tabela: ela tem de existir antes (ou use CREATE TABLE … AS SELECT). Aqui, também faltou o s de contatos.

Colunas trocadas

Sem mensagem nenhuma: os dados entram nas colunas erradas (seção 3).

Como consertar: apague o que entrou errado (com DELETE … WHERE), confira a ordem da lista e do SELECT, e rode de novo.

Nos outros bancos

INSERT INTO tabela (colunas) SELECT … é do SQL padrão e se escreve igual no MySQL, no PostgreSQL e no SQL Server. O que muda é o "pular as repetidas" (trechos ilustrativos, não rodam no SQLite):

-- MySQL
INSERT IGNORE INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

-- PostgreSQL
INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL
ON CONFLICT DO NOTHING;

Repare que no MySQL é INSERT IGNORE, sem o OR. No SQL Server não há uma forma curta; o costume é filtrar com WHERE NOT EXISTS (…) (aula EXISTS), que também funciona no SQLite. E lembre que o SELECT … INTO do SQL Server cria a tabela; para inserir numa que já existe, lá também é INSERT INTO … SELECT.

Referência rápida

Escrita	O que faz
`INSERT INTO t (a, b) SELECT x, y FROM … WHERE …;`	Insere em `t` as linhas da consulta; `x` vai para `a`, `y` para `b`.
`SELECT nome, 'cliente' …`	Valor fixo, igual em todas as linhas.
`INSERT INTO t SELECT * FROM t_copia;`	Sem lista: o `SELECT` traz todas as colunas, na ordem da tabela.
`INSERT OR IGNORE INTO t … SELECT …;`	Pula as linhas que furariam `UNIQUE` ou a chave primária.
`INSERT OR REPLACE INTO t … SELECT …;`	Substitui as linhas de mesma chave.
Regras da tabela de destino	Conferidas em cada linha; uma linha errada desfaz o comando inteiro.

Mensagem	Quer dizer
`2 values for 3 columns`	O `SELECT` devolve menos colunas que a lista do `INSERT`.
`table contatos has 5 columns but 2 values were supplied`	Sem lista, o `SELECT` precisa trazer todas as colunas.
`near "SELECT": syntax error`	`VALUES` e `SELECT` juntos: use só o `SELECT`.
`NOT NULL constraint failed: emails.email`	Alguma linha veio com NULL numa coluna obrigatória.
`UNIQUE constraint failed: contatos.email`	Alguma linha repete um valor que já existe (rodou duas vezes?).
`no such table: contato`	A tabela de destino não existe.

Exercícios

1. No lugar do VALUES

Complete para copiar os nomes dos fornecedores para a tabela contatos:

INSERT INTO contatos (nome, origem) nome, 'fornecedor' FROM fornecedores;

2. Sem duplicar

Complete para que, rodando de novo, as linhas repetidas sejam puladas sem erro:

INSERT OR INTO contatos (nome, email, origem) SELECT nome, email, 'cliente' FROM clientes WHERE email IS NOT NULL;

3. Em INSERT INTO contatos (nome, email) SELECT email, nome FROM clientes;, o que vai parar na coluna nome?

O nome, porque o SQLite casa as colunas pelo nome Nada: o comando dá erro O email, porque as colunas casam pela posição NULL

4. A tabela de destino tem email TEXT NOT NULL, e o SELECT devolve 30 clientes, 3 deles sem email. Quantas linhas entram?

Nenhuma: o comando é recusado com NOT NULL constraint failed 27 30, com os 3 emails vazios 3

5. Qual é a diferença principal entre INSERT INTO t SELECT … e CREATE TABLE t AS SELECT …?

Nenhuma O INSERT cria a tabela; o CREATE só insere O CREATE TABLE … AS confere as regras; o INSERT não O INSERT usa uma tabela que já existe, com as regras dela; o CREATE TABLE … AS cria uma tabela nova, sem regras

6. O que o SQLite responde a INSERT INTO contatos (nome, email, origem) SELECT nome, email FROM clientes;?

Insere com a origem NULL 2 values for 3 columns table contatos has 5 columns but 2 values were supplied near "SELECT": syntax error

Missão final: a agenda completa

Missão: o código cria a agenda e tenta copiar os clientes, mas dá erro: a agenda tem uma regra (o CHECK do fim, aula CHECK) que não deixa entrar ninguém sem email nem telefone, e três clientes não têm email. Conserte a cópia dos clientes para trazer só os que têm email, e acrescente um segundo INSERT INTO … SELECT com os 6 fornecedores (nome, telefone e a origem 'fornecedor'). A agenda deve terminar com 33 contatos.

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL,
    CHECK (email IS NOT NULL OR telefone IS NOT NULL)
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente'
FROM clientes;

SELECT origem, COUNT(*) AS contatos
FROM contatos
GROUP BY origem;

CREATE TABLE contatos (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    email    TEXT UNIQUE,
    telefone TEXT,
    origem   TEXT NOT NULL,
    CHECK (email IS NOT NULL OR telefone IS NOT NULL)
);

INSERT INTO contatos (nome, email, origem)
SELECT nome, email, 'cliente'
FROM clientes
WHERE email IS NOT NULL;

INSERT INTO contatos (nome, telefone, origem)
SELECT nome, telefone, 'fornecedor'
FROM fornecedores;

SELECT origem, COUNT(*) AS contatos
FROM contatos
GROUP BY origem;

Resumo

INSERT INTO t (colunas) SELECT …; insere numa tabela que já existe todas as linhas que a consulta devolve. Sem VALUES, sem parênteses em volta do SELECT.
O SELECT pode ter valores fixos ('cliente'), contas, funções, JOIN e GROUP BY.
As colunas casam pela posição: escreva a lista no INSERT e o SELECT na mesma ordem. Colunas trocadas não dão erro, dão dados errados.
As regras da tabela de destino são conferidas; se uma linha furar, nenhuma entra. Filtre com WHERE.
INSERT OR IGNORE pula as repetidas; INSERT OR REPLACE substitui as de mesma chave, útil para restaurar uma cópia.
É a forma padrão nos quatro bancos; muda só o "pular repetidas": INSERT IGNORE no MySQL, ON CONFLICT DO NOTHING no PostgreSQL.