Microsoft SQL and how to update imported data

1

Hi again. As I am the newbie in this field I couldn't find how to update imported data from Excel in Microsoft SQL management studio. I've imported 10 Tables from Excel into SQL studio and now I need to update some of the tables.
So, is there any way to do this and how?

Thank you in advance.

2

Take a look at this article: https://support.microsoft.com/en-us/kb/321686

3

Seems likely that you now have (or might have, in the future):

  • Some stale rows in Database table that are no longer in Excel
  • Some new rows in Excel that are not, yet, in Database table
  • Some rows that are in both Excel and Database table, but have changed in Excel (or maybe have changed in Database but NOT in Excel, or changed DIFFERENTLY in Excel?)
  • And finally some rows in both which have not changed (and can be ignored).

My approach would be to import the Excel spreadsheet into a separate "staging" table and then to "merge" the changes into the main table. "Merge" might be defined as:

  • Delete any rows in Main Table no longer in Staging table
  • Update any rows in Main Table which are also in Staging table (perhaps ONLY if different in one, or more, columns)
  • Insert columns into Main Table that are new in Staging table.

(This process is referred to as ETL - Extraction, Transformation, and Load)

The "problem" with this is that you need a consistent Primary Key which is unique to each row, and does not change, so that you can "align" your Main Table and Staging Table in order to make the "merge". Hopefully you have suitable column(s) which constitute a Primary Key?

4

O man, I didn't understand you, really. As I said, I am the newbie and don't know anything about above mentioned. Is there other way to explain my how to update/refresh database imported from Excel?
Thank you.

5

You do have to understand, and decide, what you want to do about rows that already exist in the database, rows that have been removed from Excel and so on.

Why don't you just throw away all the data in your existing tables, and make a fresh import (the same way as you did before)?