March 3, 2019 at 11:43 am
I have an Excel file with data in it. I want to use Data tab > MS Query window where I would write the SQL code. I want to the code to do the either of two things:
1. Loop through the Excel data row by row, then add the Excel data to records that already exist in the database (UPDATE statement)
2. Loop through the Excel data row by row, then insert the Excel data as new records in the database
Is this possible? I'm fairly certain that it is. I have used MS Query to do SELECT statements, but I have not updated existing records or added new records.
Can anybody send me links to articles or videos that describe in general how to do this?
March 4, 2019 at 1:13 pm
Normally, developers would import the data from excel into a SQL staging table, then perform adds/updates/deletes in SQL.
There are plenty of articles on how to do what I mentioned above.
March 4, 2019 at 1:42 pm
First import your spreadsheet into a staging table in your database. Then run a MERGE statement to add/update rows in your target table. Both of these steps are well-documented.
Looking at https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017 and https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ should get you started. For more, google "MSDN SQL MERGE" and "MSDN SQL IMPORT EXCEL".
When doing things in the database, try to avoid thinking "I need to write a loop." Read up on set-based processing if you are going to be doing much more of this. It will save you a lot of headaches and make your code run much faster. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 4, 2019 at 6:08 pm
The Dixie Flatline - Monday, March 4, 2019 1:42 PMFirst import your spreadsheet into a staging table in your database. Then run a MERGE statement to add/update rows in your target table. Both of these steps are well-documented.
Looking at https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017 and https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ should get you started. For more, google "MSDN SQL MERGE" and "MSDN SQL IMPORT EXCEL".
When doing things in the database, try to avoid thinking "I need to write a loop." Read up on set-based processing if you are going to be doing much more of this. It will save you a lot of headaches and make your code run much faster. Good luck.
Thank you for the links. I'll take a look.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply