Using INSERT INTO in Excel VBA. Free ExcelVBA Help Forum. An important message from the Admin team. Oz. Grid now operating on https. As part of our continuous efforts to improve your experience and security on Oz. Grid Free ExcelVBA Help Forums, we are switching over to https Secure HTTP. This change is immediate, and you should soon see a green padlock andor the word Secure in your address bar to confirm this. If you have any questions regarding this, or need to raise any issues please contact a member of the administration team. There are times when you need to create dependent lists from a dynamic range. The data for the list can come from, say an SQL Database or even a website. Interested in learning more about Excel VBA MrExcel will teach you how to use Microsoft Excel VBA. Check out our site for more information about Excel VBA tutorials. UploadFile/e628d9/inserting-retrieving-records-from-ms-access-2007-using-odbc/Images/3.gif' alt='Excel Vba Update Sql Database' title='Excel Vba Update Sql Database' />1 is it possible to query data from two different databases. If we are talking about 2 different tables in the same database, use a join. If we are talking. 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. Sample Excel Files. Free Excel workbooks that you can download, to see how functions, macros, and other features work. Some files contain VBA code, so enable macros. Excel macro tutorials for beginners Excel Macro tutorials for intermediate users. Bulk email Sending through VBA, Download FREE Excel EBook Enjoy. These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is. Insert, Update or Delete Data in SQL Server from Excel. 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. Lets work through some examples with the SQL Server 2. Adventure. Works sample database for each operation with a sample Excel spreadsheet. Prerequisites. For all of these examples, please do the following Download this Excel Office 2. 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. SaveToDB AddIn allows loading web and database data into Microsoft Excel, and saving data back to a database. Excel Vba Update Sql Database' title='Excel Vba Update Sql Database' />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. Sales. Person and Excel spreadsheet. SELECT SP. Sales. Person. ID,SP. Territory. ID,SP. Sales. Quota,SP. Bonus,SP. Commission. Pct,SP. Sales. YTD,SP. Sales. Last. Year,SP. SP. Modified. Date,T. Sales. Person. ID,T. Territory. IDFROM Adventure. Works. Sales. Sales. Person SPINNERJOINOPENROWSETMicrosoft. ExcelSQL Server ImportExport using VBA ADO and QueryTable, working VBA code download. Jet. OLEDB. 4. 0,Excel 8. DatabaseC MSSQLTips1. OPENROWSETExamples. SELECT Sales. Person. ID, Territory. ID FROM SELECTExample TON SP. Sales. Person. ID T. Sales. Person. IDAND SP. Territory. ID T. Territory. IDORDERBY SP. Sales. Person. ID, SP. Territory. IDGO INSERT with a SELECT Statement. Code Explanation With the first block of code, five records are inserted into the Adventure. Works. Sales. Sales. Person table by reading the data from the INSERTExample worksheet of the Excel spreadsheet. In the second query, the data inserted is verified. INSERTINTO Adventure. Works. Sales. Sales. PersonSales. Person. ID, Territory. ID, Sales. Quota, Bonus, Commission. Pct, Sales. YTD, Sales. Last. Year, rowguid, Modified. DateSELECT Sales. Person. ID,Territory. ID ,Sales. Quota ,Bonus ,Commission. Pct ,Sales. YTD ,Sales. Last. Year,NEWID,GETDATEFROMOPENROWSETMicrosoft. Jet. OLEDB. 4. 0,Excel 8. DatabaseC MSSQLTips1. OPENROWSETExamples. SELECT Sales. Person. ID, Territory. ID, Sales. Quota, Bonus, Commission. Pct, Sales. YTD, Sales. Last. Year FROM INSERTExampleGOSELECT FROM Adventure. Works. Sales. Sales. PersonWHERE Sales. Person. ID IN1, 2, 3, 4, 5GOUPDATE with a JOIN Statement. Mac Os X Server 10.6 Download Iso. Code Explanation With the first block of code, five records are updated in the Adventure. Works. Sales. Sales. Person table by reading the data from the UPDATEExample worksheet of the Excel spreadsheet. In the second query, the data updated is verified. UPDATE SPSET SP. Bonus T. Bonus. FROM Adventure. Works. Sales. Sales. Person SPINNERJOINOPENROWSETMicrosoft. Jet. OLEDB. 4. 0,Excel 8. DatabaseC MSSQLTips1. OPENROWSETExamples. SELECT Sales. Person. ID, Territory. ID, Sales. Quota, Bonus. FROM UPDATEExample TON SP. Sales. Person. ID T. Sales. Person. IDAND SP. Territory. ID T. Territory. IDAND SP. Sales. Quota T. Sales. Quota. GOSELECT ROM Adventure. Works. Sales. Sales. PersonWHERE Sales. Person. ID IN1, 2, 3, 4, 5GODELETE with a JOIN Statement. Code Explanation With the first block of code, five records are deleted in the Adventure. Works. Sales. Sales. Person table by reading the data from the DELETEExample worksheet of the Excel spreadsheet. In the second query, the data deleted is verified. DELETE SPFROM Adventure. Works. Sales. Sales. Person SPINNERJOINOPENROWSETMicrosoft. Jet. OLEDB. 4. 0,Excel 8. Dmv Ca Driver License Test. DatabaseC MSSQLTips1. OPENROWSETExamples. SELECT Sales. Person. ID, Territory. ID, Sales. Quota, Bonus. FROM UPDATEExample TON SP. Sales. Person. ID T. Sales. Person. IDAND SP. Territory. ID T. Territory. IDGOSELECT ROM Adventure. Works. Sales. Sales. PersonWHERE Sales. Person. ID IN1, 2, 3, 4, 5GOGeneral Analysis. In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1. 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 2. 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. Install Mini Sap Basis Support. Next Steps. Last Update 2. About the author. Since 2. 00. 2, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips. Baltimore SSUG co leader. View all my tips.