Insert, Update or Delete Data in SQL Server from Excel

By:   |   Comments (12)   |   Related: 1 | 2 | 3 | 4 | 5 | > Microsoft Excel Integration


Problem

I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data. The main command used in one of the tips is OPENROWSET. This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets. Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases. We always upload the data to a table and then begin the process. Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations? Are their any benefits to transitioning our code to another approach with the OPENROWSET command?

Solution

Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server. In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet. Let's work through some examples with the SQL Server 2005 AdventureWorks sample database for each operation with a sample Excel spreadsheet.

Prerequisites

For all of these examples, please do the following:

  • Download this Excel (Office 2003) spreadsheet to perform the operations.
  • In order to follow the examples, be sure to save the Excel spreadsheet into a directory called C:\MSSQLTips\.
  • Review each of the worksheets in the Excel file to see the different data.
  • Be sure to close the Excel spreadsheet before running any of the code.
  • Review each of the commands below before executing them in your environment.

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$]') T
ON 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 SP
SET SP.Bonus = T.Bonus
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, 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 SP
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, SalesQuota, Bonus
FROM [UPDATE_Example$]') T
ON SP.SalesPersonID = T.SalesPersonID
AND SP.TerritoryID = T.TerritoryID
GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson]
WHERE SalesPersonID IN (1, 2, 3, 4, 5)
GO

General Analysis

In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1000's of records, but that seems to be the first concern. If you test the approach and the overall performance is not an issue, then consider the approach. It also may be faster to perform a single UPDATE as is the case with the example above versus uploading (INSERT...SELECT) the data and then performing an UPDATE based on the new table. In addition, by using the commands listed above versus a two step process the overall code may be a little bit cleaner. A second consideration with SQL Server 2005 is that the Surface Area Configuration setting must be enabled to use this code in either case. Finally, with either approach be sure to clean up any Excel spreadsheets or temporary tables once you are finished with them.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, July 21, 2023 - 5:19:33 PM - Bart Back To Top (91421)
UPDATE SP
SET SP.Bonus = T.Bonus
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, 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

How to do the inverse? updfate row excel from data database sql.

Thanks.

Thursday, June 13, 2019 - 3:39:55 PM - Jeremy Kadlec Back To Top (81459)

YLI,

Please reference these tips:

https://www.mssqltips.com/sqlservertip/1430/import-excel-data-into-sql-server-using-copy-and-paste/

https://www.mssqltips.com/sqlservertip/2002/import-excel-data-with-sql-server-integration-services/

https://www.mssqltips.com/sqlservertip/2770/importing-data-from-excel-using-ssis--part-1/

https://www.mssqltips.com/sqlservertip/2772/importing-data-from-excel-using-ssis--part-2/

HTH.

Thank you,
Jeremy Kadlec

Thursday, June 13, 2019 - 2:17:27 PM - YLI Back To Top (81458)

Is there any way that i can enter data in Excel and load it into sql server directly?

Thank you very much!

YLI

Thursday, October 19, 2017 - 4:32:11 AM - nick Back To Top (68539)

 

 

Hey Jeremy, can u post a video tutorial?

Monday, November 19, 2012 - 8:21:11 PM - Ary Back To Top (20421)

 

Hey Jeremy, Thank you so much for considering to help. I figured I could accomplish that using ADODB.
Followed the link at http://msdn.microsoft.com/en-us/library/ms807027.aspx

I would love to learn how to use OLEDB too. Following are my SQL System details:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

I'm using MS Office 2010 Excel VBA.

 

Monday, November 19, 2012 - 9:40:13 AM - Jeremy Kadlec Back To Top (20410)

Ary,

Can you post the code that is causing the issue? 

What SQL Server version, service pack and edition of SQL Server are you working on?

Thank you,
Jeremy Kadlec

Sunday, November 18, 2012 - 12:13:57 PM - ary Back To Top (20401)

I followed the steps above however i get the following error:

 

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

 

Please help. I used sp_configure to enable ad hoc remote queries and OLE automation in SQL Facets. Also suggest if there is 

a way to update excel data using vba macros or otherwise without having to log in to ssms?

Tuesday, July 3, 2012 - 12:25:17 PM - richard Back To Top (18324)

Hi,

You might like to know about a different approach - you can try this for free – and includes templates and validation as well as sending data from excel to stored procedures as well as tables.

Excel to database

Thank you for your interest

 

Saturday, August 16, 2008 - 8:53:36 PM - odeddror Back To Top (1645)

Yes I did


Monday, August 11, 2008 - 8:22:29 AM - ddnikon Back To Top (1623)

Do you have a sample of the code you're using?  Might be a stupid question, but have you created the spreadsheet and used the same name in the code?

Monday, August 11, 2008 - 8:02:08 AM - odeddror Back To Top (1622)

Hi there,

Idon't know if this is the right place but

I'm using Windows Vista Business x64 SP1 and SQL Server 2005 Devloper x64 SP2

When I ran this example I'm getting (even I set in SQL configuration OPENROWSET ON)

Msg 7403, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Maybe do you have tip on this issue?

 

Thanks,

Oded Dror

Tuesday, July 29, 2008 - 6:29:49 PM - ddnikon Back To Top (1526)

I coded the example exactly as described, enabled the xp_cmdshell, and downloaded the Excel spreadsheet.  However, the SalesPersonId's were different than the Id's in the table, the table ID numbers started in the 200's ( 268, 275, 276, etc.), so I hanged them on the spreadsheet.  The query runs successfully, but no rows are updated, and cannot figure out why.  Do you have any suggestions?  Here's the code I'm using.....  Thanks for any help you can give. Don

UPDATE SP

SET SP.Bonus = T.Bonus

FROM [AdventureWorks].[Sales].[SalesPerson] SP

INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\MSSQLTips\DD_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














get free sql tips
agree to terms