Insert, Update or Delete Data in SQL Server from Excel Table

OPENROWSET Examples
Below are four examples to show some of the flexibility with the OPENROWSET command:

SELECT with a JOIN and ORDER BY Clause

Code Explanation - With the query below, 5 records should be returned to show a simple INNER JOIN statement can return a single result set from both data in the table ([Sales].[SalesPerson]) and Excel spreadsheet.

SELECT SP.[SalesPersonID] ,SP.[TerritoryID] ,SP.[SalesQuota] ,SP.[Bonus] ,SP.[CommissionPct],SP.[SalesYTD] ,SP.[SalesLastYear] ,SP.[rowguid] ,SP.[ModifiedDate] ,T.[SalesPersonID] ,T.[TerritoryID] FROM [AdventureWorks].[Sales].[SalesPerson] SP INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$]') TON SP.[SalesPersonID] = T.[SalesPersonID] AND SP.[TerritoryID] = T.[TerritoryID] ORDER BY SP.[SalesPersonID], SP.[TerritoryID] GO

INSERT with a SELECT Statement

Code Explanation - With the first block of code, five records are inserted into the  [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the INSERT_Example worksheet of the Excel spreadsheet.  In the second query, the data inserted is verified.

INSERT INTO [AdventureWorks].[Sales].[SalesPerson](SalesPersonID, TerritoryID, SalesQuota,Bonus, CommissionPct, SalesYTD, SalesLastYear, rowguid, ModifiedDate) SELECT SalesPersonID,TerritoryID ,SalesQuota ,Bonus ,CommissionPct ,SalesYTD ,SalesLastYear, NEWID(),GETDATE()FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus, CommissionPct, SalesYTD, SalesLastYear FROM [INSERT_Example$]') GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO

UPDATE with a JOIN Statement

Code Explanation - With the first block of code, five records are updated in the  [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the UPDATE_Example worksheet  of the Excel spreadsheet.  In the second query, the data updated is verified.

UPDATE SPSET SP.Bonus = T.BonusFROM [AdventureWorks].[Sales].[SalesPerson] SP INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;',  'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus FROM [UPDATE_Example$]') T ON SP.SalesPersonID = T.SalesPersonID AND SP.TerritoryID = T.TerritoryID  AND SP.SalesQuota = T.SalesQuota GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO

DELETE with a JOIN Statement

Code Explanation - With the first block of code, five records are deleted in the [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the DELETE_Example worksheet of the Excel spreadsheet.  In the second query, the data deleted is verified.

DELETE SPFROM [AdventureWorks].[Sales].[SalesPerson] SPINNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus FROM [UPDATE_Example$]') TON SP.SalesPersonID = T.SalesPersonIDAND SP.TerritoryID = T.TerritoryID GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO