INSERT INTO (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

Er zijn verschillende methodes om data in records in te voeren. Onder andere MySQL Workbench en PMA bieden hiervoor mogelijkheden. Dit artikel behandelt SQL-methodes.

Meestal zoek ik dit 

insert ignore into eindtabel (veld01, veld02)
select
   veld01 as veld01,
   veld02 as veld02
from input01;

SQL met embedde data

Data kan ingevoerd worden met ingebedde SQL-statements. Dat is een vreemde eend in de bijts, omdat data en commando's daarbij vermengd raken. Desalniettemin is het vaak een goede oplossing. Bv.:

  • In situaties waarin ik anders handmatig data zou aanpassen. Door het in een script te stoppen, is het herhaalbaar
  • Database-dumps
  • Testscripts & voorbeelden.

INSERT INTO is de standaardmanier om records in te voeren vanuit code. Er zijn twee varianten op de syntaxis:

Zonder kolomnamen

Indien je geen kolomnamen gebruikt, moeten alle kolommen worden opgenomen in VALUES(). Meerdere records kunnen in één statement worden opgenomen. Dat kun je bv. zien bij mysqldump-output. Bv.: 

INSERT INTO table_name
VALUES 
   (value1, value2, ..., valueN),
   (value1, value2, ..., valueN),
   (value1, value2, ..., valueN);

Meestal is dit geen handige aanpak: Als de definitie van de tabel verandert, werkt dit statement niet meer. Het is daarnaast moeilijk te debuggen.

Inclusief kolomnamen

Voorbeeld: 

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Inclusief kolomnamen - Specifieke kolommen

Deze syntaxis kun je gebruiken om alleen specifieke velden in te voeren. De velden die je niet benoemt, worden met null gevuld. Variant op bovenstaand statement: 

INSERT INTO Customers (CustomerName, Country)
VALUES ('Peterson','Norway');
VALUES ('Sjefke','Belgium');

Tussen gelijkvormige tabellen

Bv.: 

insert into tbl_zoekterm_tmp select * from tbl_zoekterm_tmp_exact;
insert into tbl_zoekterm_tmp select * from tbl_zoekterm_tmp_bmm;
insert into tbl_zoekterm select * from tbl_zoekterm_tmp;

Vanuit een andere tabel + subtabel

insert into term_hierarchy
select 
   tid, 
   (select tid from term_data where name = "Merk" and description="Koolborstels op merk") 
from term_data 
where description like "Alle koolborstels voor %";

Je moet (bijna altijd) kolomnamen benoemen

Als je data overfietst vanuit een bron- naar een doeltabel, en die zijn precies gelijkvormig (dus: Zelfde # kolommen + compatibele veldsoorten), dan hoef je niet de kolommen te benoemen. In alle andere gevallen is er helaas geen ontkomen aan. In het bijzonder, als bron- en doeltabellen niet gelijkvormig zijn: 

drop table if exists input01;
drop table if exists eindtabel;

create table input01(             -- Voorbeeld van snel een testtabel in elkaar draaien 
	veld01 varchar(100),
	veld02 varchar(100)
);

create table eindtabel(
	veld01 varchar(100),
	veld02 varchar(100),
	veld03 varchar(100),
	veld04 varchar(100)
);

insert into input01 (veld01, veld02) -- Voorbeeld van snel een testtabel van data voorzien
values ("a","b"),("c","d");

-- -----------------------------------------------------------------
-- Dit werkt niet: Foutmelding dat # kolommen niet correspondeert
-- -----------------------------------------------------------------
--
-- insert into eindtabel 
-- select * from input01;

-- -----------------------------------------------------------------
-- Dit werkt niet: Foutmelding dat # kolommen niet correspondeert
--
-- Da's jammer: Dit zou precies de oplossing zijn geweest
-- -----------------------------------------------------------------
--
-- insert into eindtabel
-- select
-- 	veld01 as veld01, -- Zelfs als de kolomnamen overeenkomen, wordt dat niet geaccepteerd
-- 	veld02 as veld02  -- Zelfs als de kolomnamen overeenkomen, wordt dat niet geaccepteerd
-- from input01;

-- -----------------------------------------------------------------
-- Dit werkt
-- -----------------------------------------------------------------
--
-- insert into eindtabel (veld01, veld02) -- Veldnamen tussen haakjes zijn noodzakelijk bij niet-gelijkvormige tabellen
-- select
-- 	veld01 as veld01,
-- 	veld02 as veld02
-- from input01;

-- -----------------------------------------------------------------
-- Dit werkt
-- -----------------------------------------------------------------
--
insert into eindtabel (veld01, veld02) -- Weer: Veldnamen tussen haakjes nodig
select
	veld01,
	veld02
from input01;

-- -----------------------------------------------------------------
-- Dit werkt
--
-- Maar niet helemaal as verwacht: De "as-clausule" wordt genegeerd
-- -----------------------------------------------------------------
--
insert into eindtabel (veld01, veld02)
select
	veld01 as hupseflups,
	veld02 as ietsanders
from input01;

-- -----------------------------------------------------------------
-- Dit werkt niet: Unknown column 'veld03' in 'field list'
--
-- Jammer: Je mag geen velden in het select-statement opnemen
-- die niet genoemd worden in de veldlijst.
-- -----------------------------------------------------------------
--
insert into eindtabel (veld01, veld02) -- Alle doelvelden moeten benoemd worden!
select
	veld01,
	veld02,
	veld03,
	veld04
from input01;

select * from eindtabel;

Je moet kolomnamen benoemen - Maar dit is gemakkelijker

Het probleem dat ik heb, is dat ik soms rekenbladen moet vullen met tientallen velden. Om die allemaal te moeten benoemen, is nogal onhandig. Maar wellicht is er een betere manier:

  • Gebruik een select-query om een tijdelijke tabel aan te maken en te vullen
  • Pas eventueel die tijdelijke tabel aan (bv. onnodige kolommen verwijderen)
  • Gebruik die tijdelijke tabel om de echte tabel te vullen.

Bij deze aanpak is het tweede statement namelijk een stuk korter!

Alleen unieke records invoegen

Doe de volgende twee dingen:

  • Defineer een veld als UNIQUE, of gebruik een sleutel,
  • Gebruik het keyword IGNORE in het insert-statement.

Voorbeeld: 

drop table if exists brontabel;
drop table if exists doeltabel;

create table brontabel
(
	veld01 varchar(100),
	veld02 varchar(100)
);

create table doeltabel
(
	veld01 varchar(100) unique, -- Dit veld moet uniek zijn
	veld02 varchar(100)
);

insert into brontabel(veld01, veld02) values ("a","b"),("c","d");
insert into doeltabel(veld01, veld02) values ("a","b"),("e","f");

-- Alle records uit bron invoegen in doel
-- ======================================
-- 
insert ignore into doeltabel(veld01, veld02) -- Zonder IGNORE krijg je een foutmelding
select
	veld01 as veld01,
	veld02 as veld02
from brontabel;

select * from doeltabel;

Dit werkt niet!

Terwijl het er zo mooi uitziet - Er wordt nix ingevoerd: 

drop table if exists brontabel;
drop table if exists doeltabel;

create table brontabel
(
	veld01 varchar(100),
	veld02 varchar(100)
);

create table doeltabel
(
	veld01 varchar(100),
	veld02 varchar(100)
);

insert into brontabel(veld01, veld02) values ("a","b"),("c","d");
insert into doeltabel(veld01, veld02) values ("a","b"),("e","f");

-- Alle records uit bron invoegen in doel
-- ======================================
-- 
-- insert into doeltabel(veld01, veld02)
-- select
-- 	veld01 as veld01,
-- 	veld02 as veld02
-- from brontabel;
-- select * from brontabel;
-- select * from doeltabel;
-- 
-- Alleen unieke records uit bron invoegen in doel
-- ===============================================
-- 
insert into doeltabel(veld01, veld02)
select
	veld01 as veld01,
	veld02 as veld02
from brontabel
where

	brontabel.veld01 <> doeltabel.veld01 and
	brontabel.veld02 <> doeltabel.veld02;

select * from brontabel;
select * from doeltabel;

PK

Als de doeltabel een primary key heeft (en dus non-null, auto-increment), dan is de truuk om dat veld leeg te laten in de brontabel: Null aan toevoegen of '0'. Waardes in laten staan, kan echter niet

Voorbeeld: 

-- Brontabel: PK: Mag null zijn
-- ========================================
--
ALTER TABLE `dwh`.`root_tmp` 
CHANGE COLUMN `pk` `pk` INT(11) NULL DEFAULT '0' ;

-- Brontabel: NULL invoeren voor pk
-- ================================
--
update root_tmp
set pk=null;

-- Update root_child_metabo
-- ========================
-- Nu de bron-pk NULL is, kan de volledige
-- brontabel worden ingevoegd in de doeltabel
-- Daarbij wordt automatisch voor de pk's gezorgd
-- 
insert into root_child_metabo
select * from root_tmp;

Zie ook

Bronnen

Alleen unieke records invoegen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=INSERT_INTO_(MySQL)&oldid=7375"