Import and Export Data

Start Omhoog Stored Routines Triggers Scheduled Events Performance Import and Export Data Storage Engines Transactions Server Administration Users and Access Control Oefeningen Database Schema's

Importing Records

Records kunnen aan tabellen worden toegevoegd met het INSERT statement. Echter bestaat data in diverse formaten en groottes, en het ťťn voor ťťn invoeren van deze records in geen werkbare techniek, met name wanneer men data sets met honderdduizenden records moet migreren. Om ontwikkelaars te helpen bij de aanpak van dit probleem bieden de diverse database leveranciers verschillende hulpmiddelen die het proces van het importeren en exporteren van gegevens in verschillende formaten aanzienlijk vereenvoudig, zoals:

  • Door komma's gescheiden formaten;
  • Tabs gescheiden formaten;
  • En XML-gecodeerde gegevens.
  • De instructie INSERT is niet de enige manier gegevens aan een tabel toe te voegen.

    MySQL maakt het mogelijk om meerdere records met de LOAD DATA INFILE instructie toe te voegen. Deze instructie kan worden gebruikt om:

  • onbewerkte data van een tekstbestand te lezen (welke zich bevind op op de server of de cliŽnt kant van de connectie);
  • het ontleden op basis van de kolom en rij afbakeningen, en;
  • het automatisch genereren van INSERT statements om de gegevens aan een tabel toe te voegen.
  • MSSQL maakt gebruik van een BULK INSERT instructie om meerdere records toe te voegen. Echter dient men rekening te houden met volgende:

  • het gebruik van field- en rowseperators
  • het opgeven van een bcp (bulk-copy-program) format file, indien dit nodig zou zijn om elke field-seperator afzonderlijk te aan te passen, welke nodig zou zijn in het geval van een "quoted comma delimited' bestand, of men een andere mapping moet maken met betrekking tot de velden.
  • import_passenger_data_1_quoted.txt import_passenger_data_1_unquoted.txt import_passenger_data_1.bcp
    "201","652","2009-01-20","3","Rich Rabbit","HH83282949",""
    "202","652","2009-01-27","2","Zoe Zebra","JY64940400",""
    "203","652","2009-01-27","2","Zane Zebra","JY64940401",""
    "204","652","2009-01-20","2","Barbara Bear","JD74391994",""
    "205","652","2009-01-27","3","Harriet Horse","JG74860994",""
    
    201,652,2009-01-20,3,Rich Rabbit,HH83282949,
    202,652,2009-01-27,2,Zoe Zebra,JY64940400,
    203,652,2009-01-27,2,Zane Zebra,JY64940401,
    204,652,2009-01-20,2,Barbara Bear,JD74391994,
    205,652,2009-01-27,3,Harriet Horse,JG74860994,
    
    
    11.0 
    8 
    1    SQLCHAR   0   1      "\""       0    FIRST_QUOTE ""
    2    SQLCHAR   0   11     "\",\""    1    RECORDID    ""
    3    SQLCHAR   0   11     "\",\""    2    FLIGHTID    ""
    4    SQLCHAR   0   30     "\",\""    3    FLIGHTDATE  ""
    5    SQLCHAR   0   11     "\",\""    4    CLASSID     ""
    6    SQLCHAR   0   255    "\",\""    5    PAXNAME     ""
    7    SQLCHAR   0   255    "\",\""    6    PAXREF      ""
    8    SQLCHAR   0   65535  "\"\r\n"   7    NOTE        ""

    Standaard is MySQL beperkt waarin de te importeren data mag komen te staan, het moet normaliter in de map "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/" van de server host staan. MSSQL is niet beperkt tot een bepaalde folder waaruit de data opgehaald kan worden, zo zou men bijvoorbeeld ook een UNC pad kunnen kiezen. Voor meer informatie over het gebruik van bcp en format files zie volgende websites:

  • https://msdn.microsoft.com/en-us/library/ms188365.aspx,
  • https://msdn.microsoft.com/en-us/library/ms191485.aspx,
  • https://blogs.msdn.microsoft.com/sqlserverfaq/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file/,
  • https://msdn.microsoft.com/en-us/library/ms141209.aspx,
  • http://dba.stackexchange.com/questions/916/how-do-i-copy-a-table-with-select-into-but-ignore-the-identity-property.
  • MSSQL MySQL
    SELECT * INTO imported_passengers from pax
    WHERE 1=2;
    (0 row(s) affected)
    CREATE TABLE imported_passengers 
        LIKE pax;
    (0 row(s) affected)
    BULK INSERT dbo.imported_passengers
    FROM '\\milin.cc\users\public\import_passenger_data_1_quoted.txt' 
    WITH ( 
        FIRSTROW = 1,
        FORMATFILE = 
            '\\milin.cc\users\public\import_passenger_data_1.bcp',
        KEEPIDENTITY 
        );
    (5 row(s) affected)
    BULK INSERT dbo.imported_passengers
    FROM '\\milin.cc\users\public\import_passenger_data_1_unquoted.txt' 
    WITH ( 
        FIELDTERMINATOR =',', 
        FIRSTROW = 1,
        KEEPIDENTITY,
        ROWTERMINATOR = '\n' 
        );
    (5 row(s) affected)
    LOAD DATA INFILE 
      'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/import_passenger_data_1.txt'
    INTO TABLE imported_passengers
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'; 
    -- \r = carriage return; \n = newline character
    (5 row(s) affected Records: 5 Deleted: 0 Skipped: 0 Warnings: 0)
    SELECT ClassID, PaxName
    FROM imported_passengers
    WHERE RecordID > 200;
    (5 row(s) returned)
    SELECT ClassID, PaxName
    FROM imported_passengers
    WHERE RecordID > 200;
    (5 row(s) returned)
    ClassID PaxName
    1 3 Rich Rabbit
    2 2 Zoe Zebra
    3 2 Zane Zebra
    4 2 Barbara Bear
    5 3 Harriet Horse
    ClassID PaxName
    1 3 Rich Rabbit
    2 2 Zoe Zebra
    3 2 Zane Zebra
    4 2 Barbara Bear
    5 3 Harriet Horse

    Standaard gaat MySQL ervan uit dat het gegevensbestand zich op de server host bevindt, op de locatie die in de LOAD DATA INFILE statement is opgegeven. Er is echter maar ťťn enkele specifieke locatie (of een subdirectory van deze locatie) op de server host geldig: dit is de locatie die is ingesteld in de secure_file_priv systeem variabele. U kunt deze locatie terugvinden door het uitvoeren van de volgende instructie:

    MySQL
    SHOW VARIABLES WHERE 
        Variable_Name = 'secure_file_priv';
    (1 row(s) returned)
    Variable_name Value
    1 secure_file_priv C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\

    Een typische respons op deze instructie is: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\. Als u een bestand van (of naar) een andere locatie probeert te importeren (of exporteren) dan de secure-file-priv locatie (of een subdirectory van deze locatie), dan geeft de server een foutmelding:

    Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement ;

    Opmerkingen:

  • Als secure_file_priv is ingesteld op een lege tekenreeks (""), dan zal de server zal de import- en exportbewerkingen vanuit elke bestandslocatie accepteren.
  • Als secure_file_priv is ingesteld op NULL, zal de server alle import- en exportbewerkingen uitschakelen.
  • De secure_file_priv kan niet dynamisch door middel van de SET-instructie worden gewijzigd terwijl de server draait. Om de secure_file_priv te wijzigen dient men de server te stoppen en opnieuw te starten.
  • Als men in plaats daarvan een data-bestand van de client gastheer wil gebruiken, kunt u het sleutelwoord LOCAL toevoegen aan het LOAD DATA INFILE statement om aan te geven dat MySQL het bestand op het bestandssysteem van de cliŽnt moet gebruiken. Het volgende voorbeeld toont dit door het laden van gegevens uit een bestand op de cliŽnt machine:

    MySQL
    LOAD DATA LOCAL INFILE 
      '//milin.cc/users/public/import_passenger_data_1.txt'
    INTO TABLE imported_passengers
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

    Als er minder velden in het gegevensbestand zijn dan in de tabel, of indien de waarden in het gegevensbestand niet in dezelfde volgorde zijn gerangschikt als de velden in de tabel kunt in MySQL aangeven hoe de gegevens in het gegevensbestand aan de velden van de tabel gelinkt moeten worden door het opgeven van een lijst met veldnamen na het LOAD DATA INFILE statement. MySQL zal NULL-waarden invoegen, als het ontbrekende veldwaarden tegenkomt (indien toegestaan door de veldwaarden en eventuele constraints). In MSSQL kan u met behulp van het bulk copy format bestand de mapping van de betrokken velden aanpassen.

    import_passenger_data_2_quoted.txt import_passenger_data_2_unquoted.txt import_passenger_data_2_quoted.bcp
    "Rich Rabbit","652","2009-01-20","3"
    "Zoe Zebra","652","2009-01-27","2"
    "Zane Zebra","652","2009-01-27","2"
    "Barbara Bear","652","2009-01-20","2"
    "Harriet Horse","652","2009-01-27","3"
    
    Rich Rabbit,652,2009-01-20,3
    Zoe Zebra,652,2009-01-27,2
    Zane Zebra,652,2009-01-27,2
    Barbara Bear,652,2009-01-20,2
    Harriet Horse,652,2009-01-27,3
    
    
    11.0 
    5 
    1    SQLCHAR   0   1      "\""       0    FIRST_QUOTE ""
    2    SQLCHAR   0   255    "\",\""    5    PAXNAME     ""
    3    SQLCHAR   0   11     "\",\""    2    FLIGHTID    ""
    4    SQLCHAR   0   30     "\",\""    3    FLIGHTDATE  ""
    5    SQLCHAR   0   11     "\"\r\n"   4    CLASSID     ""
    import_passenger_data_2_unquoted.bcp
    11.0 
    5 
    1    SQLCHAR   0   1      ""         0    FIRST_QUOTE ""
    2    SQLCHAR   0   255    ","        5    PAXNAME     ""
    3    SQLCHAR   0   11     ","        2    FLIGHTID    ""
    4    SQLCHAR   0   30     ","        3    FLIGHTDATE  ""
    5    SQLCHAR   0   11     "\r\n"     4    CLASSID     ""

    MSSQL MySQL
    SELECT * INTO imported_passengers from pax
    WHERE 1=2;
    (0 row(s) affected)
    CREATE TABLE imported_passengers 
        LIKE pax;
    (0 row(s) affected)
    BULK INSERT dbo.imported_passengers
    FROM '\\milin.cc\users\public\import_passenger_data_2_quoted.txt' 
    WITH ( 
        FIRSTROW = 1,
        FORMATFILE = 
            '\\milin.cc\users\public\import_passenger_data_2.bcp'    
        );
    (5 row(s) affected)
    BULK INSERT dbo.imported_passengers
    FROM '\\milin.cc\users\public\import_passenger_data_2_unquoted.txt' 
    WITH ( 
        FIRSTROW = 1,
        FORMATFILE = 
            '\\milin.cc\users\public\import_passenger_data_2_unquoted.bcp'    
        );
    (5 row(s) affected)
    LOAD DATA INFILE 
      'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/import_passenger_data_2.txt'
    INTO TABLE imported_passengers
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    (PaxName, FlightID, FlightDate, ClassID);
    (5 row(s) affected Records: 5 Deleted: 0 Skipped: 0 Warnings: 0)
    SELECT FlightID, ClassID, PaxName, PaxRef, Note
    FROM imported_passengers;
    (5 row(s) returned)
    SELECT FlightID, ClassID, PaxName, PaxRef, Note
    FROM imported_passengers;
    (5 row(s) returned)
    FlightID ClassID PaxName PaxRef Note
    1 652 3 Rich Rabbit NULL NULL
    2 652 2 Zoe Zebra NULL NULL
    3 652 2 Zane Zebra NULL NULL
    4 652 2 Barbara Bear NULL NULL
    5 652 3 Harriet Horse NULL NULL
    FlightID ClassID PaxName PaxRef Note
    1 652 3 Rich Rabbit NULL NULL
    2 652 2 Zoe Zebra NULL NULL
    3 652 2 Zane Zebra NULL NULL
    4 652 2 Barbara Bear NULL NULL
    5 652 3 Harriet Horse NULL NULL

    Exporting Records

    Net zoals je in MySQL gegevens kan importeren vanuit een bestand naar een tabel met het LOAD DATA INFILE statement, kun je ook records uit een tabel halen om in een bestand op te slaan met de SELECT ... INTO OUTFILE constructie. Deze constructie laat alles toe wat men zou kunnen doen met doen de reguliere SELECT-instructie om het vervolgens door te sturen naar een bestand. MSSQL hanteert het command-line hulpprogramma bcp.exe of de import/export wizard van de Server Managment Studio. Voor uitgebreide informatie zie: https://msdn.microsoft.com/en-us/library/ms162802.aspx, https://msdn.microsoft.com/en-us/library/ms140052.aspx.

    MSSQL MySQL
    bcp "SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport" queryout "\\milin.cc\users\public\export_airport_1.txt" -T -c -t -S HQ-ML-FS-01

     
    SELECT AirportID, AirportName
    FROM airport
    INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_1.txt'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n';
    (15 row(s) affected)

    In MySQL wordt het bestand dat opgegeven is in de INTO OUTFILE clausule weggeschreven naar het bestandssysteem van de server en het doelbestand mag nog niet bestaan. Omdat dit bestand wordt geschreven dat uitgevoerd wordt in de MySQL server proces van de gebruiker moet de gebruiker over de juiste machtigingen beschikken om bestanden naar de opgegeven locatie te kunnen schrijven. Om veiligheidsredenen laat MySQL niet toe dat het doelbestand worden geschreven naar het client bestandssysteem. De cliŽnttoepassing dient zelf de nodige bestanden uit het server-systeem halen met behulp van externe methoden.

    Opmerking: Als u de SELECT ... INTO OUTFILE gebruikt u of de LOAD DATA INFILE opdrachten, moet een gebruiker de FILE privilege hebben.

    Als u INTO DUMPFILE gebruikt in plaats van een INTO OUTFILE, schrijft MySQL slechts ťťn rij naar het bestand, zonder enige kolom of lijnbeŽindiging en zonder het uitvoeren van enige 'escape' bewerking. Dit is handig als u een BLOB waarde in een bestand wilt opslaan.

    Uiteraard kunt u een WHERE-clausule (en alle andere clausule of trefwoorden gebruiken in een normale SELECT instructie) om de uitvoer verder te beperken.

    Het volgende voorbeeld demonstreert hoe er enkel records weggeschreven worden naar het bestand export_airport_2.txt voor luchthavens met minstens drie banen:

    MSSQL MySQL
    bcp "SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport WHERE NumRunways >= 3" queryout "\\milin.cc\users\public\export_airport_2.txt" -T -c -t "," -S HQ-ML-FS-01

     
    SELECT AirportID, AirportName
    FROM airport
    WHERE NumRunways >= 3
    INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_2.txt'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n';
    (15 row(s) affected)

    Net als bij de MySQL LOAD DATA INFILE instructie, kunt u de veld en record begrenzers (field and record delimiters) opnemen voor de data die moet worden gedumpt. In MSSQL kunt u dit aanpassen door extra parameters mee te geven: -t voor de field limiters en -r voor de row terminators.

    Het volgende voorbeeld laat zien hoe u een door tabs gescheiden output bestand kan maken:

    MSSQL MySQL
    bcp "SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport WHERE NumRunways >= 3" queryout "\\milin.cc\users\public\export_airport_3.txt" -T -c -t "\t" -r "\r\n" -S HQ-ML-FS-01

     
    SELECT AirportID, AirportName
    FROM airport
    INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_3.txt'
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n';
    (15 row(s) affected)

    Tip: U kunt ook de mysqldump tool gebruiken om de inhoud van een databank of tabel naar een bestand te schrijven. Dit hulpprogramma is bedoeld voor back-up en herstel van MySQL databases.

    MySQL/MSSQL ondersteunen ook het combineren van de INSERT en SELECT-instructies om records van de ene naar de andere tabel te exporteren. Hier is een voorbeeld, waarin de namen van passagiers uit de pax tafel naar een aparte user tabel wordt gekopieerd:

    MSSQL MySQL
    SELECT     LEFT(Paxname, CHARINDEX(' ', Paxname)) AS FirstName,
        RIGHT(Paxname, LEN(Paxname)-(CHARINDEX(' ', Paxname)-1)) AS LastName
    INTO "user"
    FROM pax;
    (6 row(s) affected)
    CREATE TABLE user (
        FirstName VARCHAR(255),
        LastName VARCHAR(255)
        );
    (0 row(s) affected)
    INSERT INTO user (FirstName, LastName)
    SELECT SUBSTRING_INDEX(PaxName, ' ', 1), SUBSTRING_INDEX(PaxName, ' ', -1)
    FROM pax;
    (6 row(s) affected Records: 6 Duplicates: 0 Warnings: 0)

    Uiteraard kunt u ook een WHERE clausule aan de SELECT-instructie toevoegen om alleen een subset van de oorspronkelijke tabel naar de nieuwe tabel te kopiŽren:

    MSSQL MySQL
    SELECT     LEFT(Paxname, CHARINDEX(' ', Paxname)) AS FirstName,
        RIGHT(Paxname, LEN(Paxname)-(CHARINDEX(' ', Paxname)-1)) AS LastName
    INTO "user"
    FROM pax
    WHERE ClassID = 2;
    (3 row(s) affected)
    CREATE TABLE user (
        FirstName VARCHAR(255),
        LastName VARCHAR(255)
        );
    (0 row(s) affected)
    INSERT INTO user (FirstName, LastName)
    SELECT SUBSTRING_INDEX(PaxName, ' ', 1), SUBSTRING_INDEX(PaxName, ' ', -1)
    FROM pax
    WHERE ClassID = 2;
    (3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0)