T SQL join example needed to understand

Question

Following:

MERGE dbo.commissions_history AS target USING (SELECT @amount, @requestID) AS source (amount, request) ON (target.request = source.request) WHEN MATCHED THEN UPDATE SET amount = source.amount WHEN NOT MATCHED THEN INSERT (request, amount) VALUES (source.request, source.amount); 

from overflow.site/questions/104540 / ... is a pretty nifty way to do an insert / update (and delete with some extra work). It’s hard for me to follow, although even after some search queries.

Someone might:

• explain it a little in simple words - the MSDN documentation has distorted my brain in this case.
• show me how it can be changed so that the user can enter values ​​for the sum and query instead of being selected from another database location?

Basically, I would like to use this to insert / update from a C # application with the information retrieved from the XML files that I receive. So, I need to understand how I can formulate a query manually to get my analyzed data in a database using this mechanism.

Answer 1

If you are not familiar with join statements , then you need to start. Understanding how connections work is the key to the rest. When you are familiar with joins, understanding merging is easiest, seeing it as a complete connection with instructions on what to do for strings that execute or don't match.

So, using the provided code example, you can see the commissions_history table

 | Amount | Request | <other fields | -------------------------------------------- | 12.00 | 1234 | <other data | | 14.00 | 1235 | <other data | | 15.00 | 1236 | <other data | 

The merge operator creates a complete join between a table called "target" and an expression that returns a table (or a result set that logically looks very much like a table like CTE) called a "source".

In the above example, it uses variables as the source, which we assume was set by the user or passed as a parameter.

 DECLARE @Amount Decimal = 18.00; DECLARE @Request Int = 1234; MERGE dbo.commissions_history AS target USING (SELECT @amount, @requestID) AS source (amount, request) ON (target.request = source.request) 

Creates the next result set when considered a union.

 | Amount | Request | <other fields | Source.Amount | Source.Request | ------------------------------------------------------------------------------ | 12.00 | 1234 | <other data | 18.00 | 1234 | | 14.00 | 1235 | <other data | null | null | | 15.00 | 1236 | <other data | null | null | 

Using instructions on what to do with the goal, provided that a match is found.

 WHEN MATCHED THEN UPDATE SET amount = source.amount 

The final target table now looks like this. Query string 1234 is updated to 18.

 | Amount | Request | <other fields | -------------------------------------------- | 18.00 | 1234 | <other data | | 14.00 | 1235 | <other data | | 15.00 | 1236 | <other data | 

Since the match did not find anything else. But let's say that the values ​​from the source were like that.

 DECLARE @Amount Decimal = 18.00; DECLARE @Request Int = 1239; 

The final connection will look like this:

 | Amount | Request | <other fields | Source.Amount | Source.Request | ------------------------------------------------------------------------------ | 12.00 | 1234 | <other data | null | null | | 14.00 | 1235 | <other data | null | null | | 15.00 | 1236 | <other data | null | null | | null | null | null | 18.00 | 1239 | 

Since a matching string was not found in the target, the statement executes another sentence.

 WHEN NOT MATCHED THEN INSERT (request, amount) VALUES (source.request, source.amount); 

The result in the target table, which now looks like this:

 | Amount | Request | <other fields | -------------------------------------------- | 12.00 | 1234 | <other data | | 14.00 | 1235 | <other data | | 15.00 | 1236 | <other data | | 18.00 | 1239 | <other data | 

Merge Operators True potential is when large tables are the source and target. Because it can do a lot of updates and / or inserts for each row with a simple simple statement.

Last note. It is important to keep in mind that not matched by default matches the full not matched by target clause, however you can specify not matched by source instead or in addition to the default clause. The merge operator supports both types of mismatch (records in the source not in the target or records in the target not in the source, as defined in the on section). You can find full documentation, limitations, and full syntax on MSDN.

Answer 2

In this example of the answer you made

 DECLARE @Request Int 

but calling it in SQL as follows:

 SELECT @amount, @requestID 

Another would be naming and calling variables the same way:

 @amount vs. Amount -> @Amount & Amount