Salta el contingut

INSERT INTO

Inserció d'una fila

La forma mes senzilla de INSERT INTO insereix una única fila especificant les columnes i els valors:

-- Especificant totes les columnes (excepte les autogenerades)
INSERT INTO clients (dni, nom, cognoms, email, telefon, data_alta)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.garcia@example.com', '972123456', '2025-09-01');

-- Podeu ometre columnes que tinguin DEFAULT o acceptin NULL
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('87654321B', 'Maria', 'Lopez Serra', 'maria.lopez@example.com');
-- data_alta prendrà el valor DEFAULT (CURRENT_DATE)
-- telefon serà NULL
-- actiu serà TRUE (DEFAULT)

-- Recuperar l'id generat (RETURNING, específic de PostgreSQL)
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('11111111C', 'Pere', 'Bosch Vila', 'pere.bosch@example.com')
RETURNING id_client;

INSERT INTO clients (dni, nom, cognoms, email, telefon, data_alta)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.garcia@example.com', '972123456', '2025-09-01');

-- Ometre columnes amb DEFAULT
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('87654321B', 'Maria', 'Lopez Serra', 'maria.lopez@example.com');

-- Recuperar l'id generat
SELECT LAST_INSERT_ID();

INSERT INTO clients (dni, nom, cognoms, email, telefon, data_alta)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.garcia@example.com', '972123456', '2025-09-01');

-- Recuperar l'id generat
SELECT SCOPE_IDENTITY() AS nou_id;
-- O bé usar OUTPUT:
INSERT INTO clients (dni, nom, cognoms, email)
OUTPUT INSERTED.id_client
VALUES ('11111111C', 'Pere', 'Bosch Vila', 'pere.bosch@example.com');

INSERT INTO clients (dni, nom, cognoms, email, telefon, data_alta)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.garcia@example.com', '972123456', DATE '2025-09-01');

-- Ometre columnes amb DEFAULT
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('87654321B', 'Maria', 'Lopez Serra', 'maria.lopez@example.com');

-- Recuperar l'id generat (RETURNING INTO, en scripts PL/SQL o SQL*Plus)
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('11111111C', 'Pere', 'Bosch Vila', 'pere.bosch@example.com')
RETURNING id_client INTO :nou_id;

Inserció de múltiples files

Inserir múltiples files en una sola instrucció és molt més eficient que fer múltiples INSERT individuals, perquè el SGBD processa tota la inserció en una sola operació:

INSERT INTO productes (id_categoria, nom, preu, estoc)
VALUES
    (1, 'Ordinador portable', 899.99, 15),
    (1, 'Ratolí sense fils', 29.99, 50),
    (1, 'Teclat mecanic', 79.99, 30),
    (2, 'Auriculars Bluetooth', 49.99, 25),
    (2, 'Altaveu portàtil', 39.99, 20);

INSERT INTO productes (id_categoria, nom, preu, estoc)
VALUES
    (1, 'Ordinador portable', 899.99, 15),
    (1, 'Ratolí sense fils', 29.99, 50),
    (1, 'Teclat mecanic', 79.99, 30);

INSERT INTO productes (id_categoria, nom, preu, estoc)
VALUES
    (1, N'Ordinador portable', 899.99, 15),
    (1, N'Ratolí sense fils', 29.99, 50),
    (1, N'Teclat mecanic', 79.99, 30);
-- N'' prefix per a NVARCHAR amb caràcters especials

-- Oracle usa INSERT ALL per a múltiples files (fins Oracle 21c)
-- Oracle 23c+ admet la sintaxi VALUES (...), (...) estàndard
INSERT ALL
    INTO productes (id_categoria, nom, preu, estoc) VALUES (1, 'Ordinador portable', 899.99, 15)
    INTO productes (id_categoria, nom, preu, estoc) VALUES (1, 'Ratolí sense fils', 29.99, 50)
    INTO productes (id_categoria, nom, preu, estoc) VALUES (1, 'Teclat mecanic', 79.99, 30)
SELECT 1 FROM DUAL;
-- La clàusula SELECT ... FROM DUAL és obligatòria a INSERT ALL

INSERT ... SELECT

Permet inserir dades a partir del resultat d'una consulta SELECT. Molt útil per a migracions, còpies de dades entre taules, poblament de taules d'arxiu, etc.

-- Copiar els clients actius a una taula d'arxiu
INSERT INTO clients_actius_backup (id_client, nom, cognoms, email, data_alta)
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = TRUE;

-- Crear una taula resum i poblar-la
INSERT INTO resum_vendes_mensual (any, mes, id_categoria, total_vendes, num_comandes)
SELECT
    EXTRACT(YEAR FROM co.data_com) AS any,
    EXTRACT(MONTH FROM co.data_com) AS mes,
    p.id_categoria,
    SUM(lc.quantitat * lc.preu_unitari) AS total_vendes,
    COUNT(DISTINCT co.id_comanda) AS num_comandes
FROM comandes co
JOIN linies_comanda lc ON co.id_comanda = lc.id_comanda
JOIN productes p ON lc.id_producte = p.id_producte
WHERE co.estat = 'entregada'
GROUP BY EXTRACT(YEAR FROM co.data_com), EXTRACT(MONTH FROM co.data_com), p.id_categoria;

INSERT INTO clients_actius_backup (id_client, nom, cognoms, email, data_alta)
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

INSERT INTO clients_actius_backup (id_client, nom, cognoms, email, data_alta)
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

INSERT INTO clients_actius_backup (id_client, nom, cognoms, email, data_alta)
SELECT id_client, nom, cognoms, email, data_alta
FROM clients
WHERE actiu = 1;

-- Poblar taula resum
INSERT INTO resum_vendes_mensual (any_v, mes, id_categoria, total_vendes, num_comandes)
SELECT
    EXTRACT(YEAR FROM co.data_com),
    EXTRACT(MONTH FROM co.data_com),
    p.id_categoria,
    SUM(lc.quantitat * lc.preu_unitari),
    COUNT(DISTINCT co.id_comanda)
FROM comandes co
JOIN linies_comanda lc ON co.id_comanda = lc.id_comanda
JOIN productes p ON lc.id_producte = p.id_producte
WHERE co.estat = 'entregada'
GROUP BY EXTRACT(YEAR FROM co.data_com), EXTRACT(MONTH FROM co.data_com), p.id_categoria;

UPSERT (INSERT ON CONFLICT / REPLACE)

L'UPSERT (UPDATE + INSERT) insereix una fila si no existeix, o l'actualitza si ja existeix (basant-se en una clau única). Evita haver de fer primer un SELECT per comprovar si el registre ja existeix.

-- ON CONFLICT DO NOTHING: ignora si ja existeix
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('12345678A', 'Joan', 'Garcia', 'joan@example.com')
ON CONFLICT (dni) DO NOTHING;

-- ON CONFLICT DO UPDATE: actualitza si ja existeix
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.actualitzat@example.com')
ON CONFLICT (dni)
DO UPDATE SET
    nom = EXCLUDED.nom,
    cognoms = EXCLUDED.cognoms,
    email = EXCLUDED.email;
-- EXCLUDED referència els valors que s'haurien inserit

-- INSERT IGNORE: ignora errors de clau duplicada
INSERT IGNORE INTO clients (dni, nom, cognoms, email)
VALUES ('12345678A', 'Joan', 'Garcia', 'joan@example.com');

-- INSERT ... ON DUPLICATE KEY UPDATE (MySQL)
INSERT INTO clients (dni, nom, cognoms, email)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.actualitzat@example.com')
ON DUPLICATE KEY UPDATE
    nom = VALUES(nom),
    cognoms = VALUES(cognoms),
    email = VALUES(email);

-- REPLACE INTO: elimina i reinsereix (perd l'id original)
REPLACE INTO clients (dni, nom, cognoms, email)
VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.nou@example.com');

-- MERGE: el UPSERT de SQL Server
MERGE INTO clients AS target
USING (VALUES ('12345678A', 'Joan', 'Garcia Puig', 'joan.nou@example.com'))
    AS source (dni, nom, cognoms, email)
ON target.dni = source.dni
WHEN MATCHED THEN
    UPDATE SET nom = source.nom, cognoms = source.cognoms, email = source.email
WHEN NOT MATCHED THEN
    INSERT (dni, nom, cognoms, email)
    VALUES (source.dni, source.nom, source.cognoms, source.email);

-- Oracle usa MERGE (com SQL Server)
MERGE INTO clients target
USING (SELECT '12345678A' AS dni, 'Joan' AS nom,
              'Garcia Puig' AS cognoms, 'joan.nou@example.com' AS email
       FROM DUAL) source
ON (target.dni = source.dni)
WHEN MATCHED THEN
    UPDATE SET nom = source.nom, cognoms = source.cognoms, email = source.email
WHEN NOT MATCHED THEN
    INSERT (dni, nom, cognoms, email)
    VALUES (source.dni, source.nom, source.cognoms, source.email);

AC0372/05/01 — Miniactivitat

RA5 · CA5.1, CA5.2

Practiqueu els tres tipus d'INSERT:

  1. Inseriu 5 productes nous a la vostra BD de pràctiques, especificant totes les columnes.
  2. Inseriu 10 usuaris en un sol INSERT multi-fila.
  3. Creeu una taula usuaris_backup amb la mateixa estructura que usuaris i copieu-hi tots els usuaris actius usant INSERT ... SELECT.
  4. Intenteu inserir un usuari amb un email que ja existeix. Que passa? Useu ON CONFLICT / ON DUPLICATE KEY per gestionar el cas.