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?
SolutionYes - 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:
...
Next Steps
If you are faced with a need to upload Excel based data to SQL Server consider the code samples in this tip as an option to do so.
As alternatives to these commands, you can also check out these tips:
Export data from SQL Server to Excel
Different Options for Importing Data into SQL Server
Importing Excel data with SQL Server Integration Services (SSIS) and dealing with unicode and non-unicode data issues
MSSQLTips.com Category: SQL Server Integration Services
MSSQLTips.com Category: Data Transformation Services
MSSQLTips.com Category: Microsoft Office Integration
One item to keep in mind with any process is to make sure all of the supporting files, tables, etc are organized and/or removed once they are no longer needed.Related Tips
No comments:
Post a Comment