Import non matching values from Excel into a table

1

I need to update a SQL table each day from an excel sheet. The complication is that each day new identifiers may be created in the excel sheet.

Therefore my first step is to check for new, non matching values in my excel sheet and add them to my SQL table, then I can use a simple update command.

I know how to do this between SQL tables by using the MERGE INTO command, does this mean I need to create a temporary table from my excel data and then use this?

If that is the case then does anyone have some code to create a temporary table from a single column in an excel sheet?

As a very new starter to SQL and a non-developer/coder I appreciate any help you can give.

Thanks,

Ed

2

Load the entire excel sheet into a staging table. You can use a temp table or a permanent table.

UPDATE matching rows first. Then,
INSERT new rows.

I recommend not using MERGE, since it's been shown to often have performance and other issues.

3

@ScottPletcher,
Can you site where i can research how it's been shown to have performance and other issues. Personally I've had no issues using MERGE from SQL Server 2008 on.

4

Thanks Scott, the temp table solution was the one i was thinking would be best. I've been struggling to find a method of creating that table which works.

Probably me missing something simple.

Do you have any code syntax which works please?

All help is gratefully appreciated.

Ed

5

Try Googling "sql server merge performance issues".

Besides that, on top of those issues, I personally find the syntax / structure of MERGE somewhat overly involved. So, for now, I'll continue to recommend that people stick to "UPSERT"s (UPDATE followed by INSERT). If you haven't seen such issues, you might prefer to continue to risk using MERGE.

For example:


"However, MERGE originally shipped with several "wrong results" and other bugs - some of which have been addressed, and some of which continue to exist even in the early preview releases of SQL Server 2014. People also tend to make some leaps of faith regarding atomicity - they don't realize that the single statement actually performs different operations separately, and thus can suffer from issues due to concurrency and race conditions just like separate statements can."