INSERT INTO (MySQL)
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 (eindtabel_veld01, eindtabel_veld02) select inputtabel_veld01 as eindtabel_veld01, inputtabel_veld02 as eindtabel_veld02 from inputtabel;
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. Zelfs als de kolomnamen wél overeenkomen. Helaas:
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
- http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/
- http://dev.mysql.com/doc/refman/5.7/en/insert-select.html
- http://dev.mysql.com/doc/refman/5.7/en/insert-select.html
- http://stackoverflow.com/questions/8297484/insert-into-select-without-detailing-all-columns → Je moet kolomnamen altijd benoemen
Alleen unieke records invoegen
- https://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/ - Algemeen SQL, niet specifiek MySQL