merge statement when not matched by source then insert to another table

Question

I have created two tables customersrc and customertemp with the columns:

customertemp

ID  name    age addr    cityid  isactive
34  Gi      24  Chennai     1   1

customersrc

CustomerId  CustomerName    CustomerAge CustomerAddress
1            Gi               24    madurai
2            Pa               23    Tirupur
3            MI               27    Tirupur

Now I need to insert pa and mi data value to the temp table bcz it is not matched with the rows of customertemp. And the row gi data will be updated which was matched.

I used the following MERGE statement

DECLARE @cityid INT SET @cityid=1

MERGE Temp.dbo.customersrc as src_customer
USING ( SELECT CustomerName,CustomerAge,CustomerAddress FROM customertemp) as temp_customer
ON src_customer.name=temp_customer.CustomerName 
    AND
    src_customer.cityid=@cityid
WHEN MATCHED THEN 
    UPDATE SET 
                src_customer.age=temp_customer.CustomerAge,
                src_customer.addr=temp_customer.CustomerAddress,
                src_customer.isactive=1

WHEN NOT MATCHED BY SOURCE THEN 
     UPDATE SET src_customer.isactive=0 ;  -- here i need the insert statement to insert in another table

Questions:

1. is it possible to write insert statement inside the when not matched by source query?
2. if it is not possible then how to achieve this using merge?
3. in a simple set theory I need to put the customersrc(table_B)-customertemp (table_A). B-A value into the another or temp table.

Answer 1

One of the main usages of the MERGE statement is to perform so called "UPSERTS" (Update matching records, insert new records), so it is definitely possible to do what you want. Just add the following to the last part of your MERGE statement:

WHEN NOT MATCHED BY TARGET THEN 
    INSERT (name, age, addr, cityid, isactive)
    VALUES (CustomerName, CustomerAge, CustomerAddress, @cityid, 1)

If you also need to insert data into a 3rd table, depending on whether rows are updated or inserted, you can use the OUTPUT clause of the merge statement. Check out the documentation: http://technet.microsoft.com/en-us/library/ms177564.aspx

Answer 2

Me: Why do you want to insert to another table?

You: To show the user who are not in the customertemp table.

So your requirement is not to insert into another table. Your requirement is to get the missing users.

You could do that with a dummy UPDATE (SET SomeCol = SomeCol) and OUTPUT. But that is a hack that I would try to avoid.

It is probably easier to do this in two statements. Here's how you'd get the missing rows:

SELECT temp_customer.*
FROM (SELECT CustomerName,CustomerAge,CustomerAddress FROM customertemp) as temp_customer
LEFT JOIN customersrc ON src_customer.name=temp_customer.CustomerName AND src_customer.cityid=@cityid
WHERE customersrc.cityid IS NULL