Relationele data importeren in SQL vanuit Excel

In SQL Server kan je snel data importeren vanuit Excel-bestanden met behulp van OPENROWSET. Deze functie impoteert een werkblad in het Excel-bestand en plaatst de inhoud in een query, die je kan SELECT-en in een tabel.

Zie: 

    SELECT O.* INTO #TempImportTable
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\Items.xlsx;HDR=YES;IMEX=1;', 'SELECT * FROM [Blad1$]') AS O;

Excel is een krachtig programma om data mee te bewerken en iedereen kan ermee overweg. Het is echter een spreadsheet en geen relationele database. In SQL koppel je tabellen aan elkaar, die in een bepaalde relatie staan tot elkaar. Je kan een lijst van drie items hebben, die in een bepaalde volgorde gekoppeld zijn aan een ander item. Excel is hiervoor niet gemaakt.

Hieronder geef ik een oplossing voor het importeren van relaties in SQL vanuit Excel.

Ten eerste moeten we een bepaalde conventie hanteren voor de relaties in Excel. Je kan één kolom gebruiken en daarin kommagescheiden identifiers zetten. Kán vanuit data, maar vanuit het beheer in Excel niet wenselijk. Dan kan je andere werkbladen nemen of tabellen in Excel, maar ook dat werkt niet fijn in Excel en maakt het importeren moeilijk. Ik kies ervoor om _[type_relatie]_[volgorde_in_tabel] te hanteren:

Voor dit voorbeeld maak ik een simpel datamodel in SQL:

En importeer ik deze data:

SQL:   

DROP TABLE IF EXISTS #TempImportTable;

    SELECT O.* INTO #TempImportTable
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\Items.xlsx;HDR=YES;IMEX=1;', 'SELECT * FROM [Blad1$]') AS O;

    MERGE Items
        AS T

    USING(
        SELECT ItemId, Name FROM #TempImportTable
    )
        AS S

        ON S.ItemId = T.ItemId

    WHEN MATCHED THEN
        UPDATE SET
            Name = S.Name

    WHEN NOT MATCHED BY TARGET THEN
        INSERT (ItemId, Name)
        VALUES(
            S.ItemId,
            S.Name
        )
    ;

    SELECT * FROM Items;
    SELECT * FROM SubItems;

Resultaat: