Snel rijen importeren in SQL-database met BULK INSERT

Voor het aanpassen van producten in je webshop, is het vaak voldoende om de wijzigingen door te voeren in het beheer, waarna je het resultaat direct ziet op de website. Iets dergelijks geldt ook voor pagina’s op je site. Deze kan je via relatief eenvoudige acties  aanmaken, bewerken en verwijderen.

In sommige gevallen wil je echter vele duizenden producten of pagina’s bewerken of aanmaken op je site. Dit handmatig doen via een control panel kost veel, té veel tijd en vraagt veel processorkracht van de server.

Dit kan bijvoorbeeld het geval zijn bij het vullen van een nieuwe webshop of het periodiek bijwerken van producten in een grote catalogus. In deze situaties zou je willen dat je met één bestand en één druk op de knop de gehele database kunt updaten.

Oplossingen zijn een importscript dat wordt uitgevoerd door de application server, wat ook erg veel rekenkracht vereist, of gebruikmaken van de ingebouwde functionaliteiten van de databaseserver. Microsoft SQL-server heeft hiervoor een speciale SQL Server Import and Export Wizard, waarmee het importeren van grote bestanden vereenvoudigd wordt, maar beschikt ook over een SQL statement om de ontwikkelaar de middelen te geven grote bestanden te importeren en de data daarna te bewerken. Deze bewerkingen vinden plaats op de databaseserver en niet op de application server, waardoor de performance van de website er niet onder te lijden heeft.

BULK INSERT

De statement die hiervoor gebruikt wordt is BULK INSERT en kan via gewone queries of in stored procedures worden uitgevoerd. Als argumenten geef je in ieder geval de tabel op waarin je wilt importeren en de naam van het bestand waarin de brongegevens staan. Daarnaast zijn er aanvullende argumenten op te geven zoals het scheidingsteken om kolommen te onderscheiden en de eerste rij die gebruikt moet worden voor de daadwerkelijke gegevens.

Een typische opdracht ziet er als volgt uit:

BULK INSERT products FROM 'c:\producten.csv' with (FIRSTROW = 2, FIELDTERMINATOR=';', ROWTERMINATOR = '\n');

Hiermee geef je aan dat:

1. Het bronbestand c:\producten.csv is,
2. De gegevens worden geïmporteerd in de tabel products,
3. De eerste rij waarin de gegevens staan, rij 2 is. Waarschijnlijk staan in de eerste rij kolomnamen,
4. Kolommen worden gescheiden met een puntkomma,
5. De volgende rij na \n (line break) begint.

Op deze manier worden de gegevens uit het bestand direct in de tabel products geplaatst, maar vaak zal je eerst bepaalde bewerkingen willen uitvoeren met de gegevens en dan heeft het zin om een tussentabel te gebruiken. De tussentabel kan éénmalig worden aangemaakt per opdracht.

Hieronder een voorbeeld, waarin drie producten in een tussentabel worden geïmporteerd:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='tmp_products') DROP TABLE tmp_products;


CREATE TABLE tmp_products

(

      productID int,

      name nvarchar(255)

);

     

BULK INSERT tmp_products FROM 'c:\producten.csv' with (FIRSTROW = 2, FIELDTERMINATOR=';', ROWTERMINATOR = '\n');


select * from tmp_products;

Het resultaat hiervan zal zijn een tabel met twee kolommen, die gebruikt kan worden in een MERGE-statement of op andere wijzen.

Gegeven dit bronbestand:

Als de query met BULK INSERT wordt uitgevoerd, krijg je het volgende resultaat:

Bij het opstellen van een nieuwe query zal je snel problemen tegenkomen, let in dat geval op de volgende zaken:

1. Gebruik de juiste delimiter,
2. Let op welk regeleinde je gebruikt,
3. Let erop dat je FIRSTROW gebruikt als de gegevens in het bronbestand niet op de eerste regel beginnen.

Het voorbeeld hierboven is eenvoudig, voor meer complexe bronbestanden kan je het argument FORMATFILE opgeven. Hierin geef je een bestand op waarin meer gedetailleerd de kolommen staan beschreven.

Meer weten over het importeren van veel producten in je webshop? Vraag Sigma Solutions. Neem dan contact op met Sigma Solutions via de mail of door op deze pagina het formulier in te vullen.