How do I add an additional when matched or how do I export certain column information to a third table?

Question

0.00/5 (No votes)

See more:

I am using the match and merge statement from sql. It works great between 2 tables, which is what it was originally designed for. However, I am looking to insert data when matched to a third possibly 4th table.

Here is the merge statement:

SQL

Merge into [table1] as T using [table2] as S on T.Last_name = S.Last_name and T.First_name = S.First_name
When matched then update set T.Last_name = S.Last_name, T.First_name = S.First_name;

What I want is to insert the ID and reference column value of all matched rows to table 3. that way I can reference the table by the ID number which is unique.

What I have tried:

I have tried to add another when matched but it does not do anything. Mind you this code is in combination with vb.net code that I am running.

Posted 5-Jul-18 5:11am

Member 11856456

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13896879 · Answer 1 · 2018-07-11T02:21:00

Solution 1

You can use the output clause from the merge statement into a temp table then filter the results and add them into your third table.

Example of Merge Output

-----Inserting data when no match found.
MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN NOT MATCHED THEN
   INSERT
   (
      [DepartmentID],
      [Name],
      [GroupName],
      [ModifiedDate]
   )
   VALUES
   (
      src.[DepartmentID], src.[Name], src.[GroupName], src.[ModifiedDate]
   )
OUTPUT
   $action,
   inserted.*,
   deleted.*;

Posted 11-Jul-18 2:21am

Member 13896879

Comments

Member 11856456 11-Jul-18 13:31pm

I see that the code is using the source information for the insert, but where is it being sent to for the output? Can I specify a table for the out put and can I do this on a when matched statement?

Member 13896879 12-Jul-18 3:15am

The output from the merge statement will be rows that have been inserted or deleted. The way does updates or for these purposes, matches will be to delete the row then insert the same row with the updated value maintaining the primary key values. Also worth noting the $action will denote what action was taken on the row sop INSERT, UPDATE or DELETE.

Below is some more information on the $action column. Hopefully this will help you sift the results so you can commit them to your third table

$action
Is available only for the MERGE statement. Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.