Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet
July 16th 2008 04:25
Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet
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:
SalesPersonID TerritoryID
1 2
2 4
3 3
4 6
5 5
Excel spreadsheet above:
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.OLE DB.4.0',
'Excel 8.0
atabase=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.OLE DB.4.0',
'Excel 8.0atabase=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.OLE DB.4.0',
'Excel 8.0atabase=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.OLE DB.4.0',
'Excel 8.0atabase=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.
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:
SalesPersonID TerritoryID
1 2
2 4
3 3
4 6
5 5
Excel spreadsheet above:
Download this Excel (Office 2003) spreadsheet to perform the operations.
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.OLE DB.4.0',
'Excel 8.0
atabase=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.OLE DB.4.0',
'Excel 8.0
atabase=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.OLE DB.4.0',
'Excel 8.0
atabase=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.OLE DB.4.0',
'Excel 8.0
atabase=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.
| 55 |
| Vote |
Add To: 
