Insert Into... Merge... Select (SQL Server)

Merge insert with select statement

According to MSDN docs

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

The syntax with INSERT from SELECT like:

  WHEN NOT MATCHED THEN
            INSERT itmid SELECT itmid FROM Table3 WHERE id=@id;

Is not allowed!

I would try to solve your problem doing another merge with Table3.

Insert Into... Merge... Select (SQL Server)

The trick is to populate the table with the MERGE statement instead of an INSERT...SELECT. That allowes you to use values from both inserted and source data in the output clause:

MERGE INTO Table3 USING
(
    SELECT null as col2, 
           110 as col3, 
           Table1.ID as col4, 
           Table2.Column2 as col5,
           Table2.Id as col6
    FROM Table1
    JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
    JOIN Table2 on Table1Table2Link.Column2=Table2.ID
) AS s ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (Column2, Column3, Column4, Column5)
VALUES (s.col2, s.col3, s.col4, s.col5)
OUTPUT Inserted.ID, s.col6
INTO @MyTableVar (insertedId, Table2Id); 

How can I use insert with select from other table in a merge statement?

You wouldn't. You have already defined S so you can reference those columns in the VALUES clause. You also don't put the alias of the destination table prior to the destination columns:

    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)

SQL MERGE - when not matched insert by using select table

Since most of your values are columns from the source table anyway - I don't see any reason why you would need a SELECT in the INSERT - just try this:

WHEN NOT MATCHED BY TARGET THEN
    INSERT (UserID, OnProcessItem, Amount, Remarks, TranNo, IfOthers)
    VALUES (@ResignEmp, b.OnProcessItem, b.Amount, b.Remarks, @TranNo, b.IfOthers);

SQL Merge Into with multiple statements

Normally you can INSERT only into one table. The syntax does not allow multiple statements in the same "when" condition.

But, SQL Server has OUTPUT clause which allows to add another table. It is very handy when you need to have some sort of auditing trail.

See this question How to INSERT into multiple tables from one SELECT statement

So, add OUTPUT clause to your MERGE statement. Something like this:

MERGE TargetProducts AS Target
USING SourceProducts    AS Source
ON Source.ProductID = Target.ProductID
    
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)
    
-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName  = Source.ProductName,
    Target.Price        = Source.Price
    
-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE

OUTPUT inserted.ProductID, inserted.ProductName, inserted.Price
INTO anotherTable (OldProductID,OldProductName, OldPrice)
;

This will capture both updates and inserts in anotherTable. To capture only inserts you can output at first into a temp table and then filter results by MERGE $action.

Have a look at this question:

Pipes and filters at DBMS-level: Splitting the MERGE output stream

Merge not inserting new values

If you look at the MERGE documentation, you will see that the source data must exist in order to match (or not match) against existing rows in the target table:

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Specifies that a row is inserted into target_table for every row
returned by <table_source> ON <merge_search_condition> that doesn't
match a row in target_table, but satisfies an additional search
condition, if present. The values to insert are specified by the
<merge_not_matched> clause. The MERGE statement can have only one WHEN
NOT MATCHED [ BY TARGET ] clause.

The problem you're facing is that your "source" data is not returning anything and so the MERGE query has nothing to match against or insert.

Sample code below to demo:

IF OBJECT_ID('dbo.TARGET_TABLE', 'U') IS NOT NULL DROP TABLE dbo.TARGET_TABLE 
GO 

CREATE TABLE TARGET_TABLE ([Name] VARCHAR(100), file_first_upload BIT, upload_date DATETIME, [STATUS] VARCHAR(100)) 

MERGE [dbo].[TARGET_TABLE] AS Target 
USING 
(SELECT [NAME]
    FROM [dbo].[TARGET_TABLE] 
    WHERE [NAME]='ThisValuesDoesntExists' AND [STATUS] IS NULL) AS Source
    ON Target.[NAME]= Source.[NAME]
WHEN NOT MATCHED
    THEN INSERT ([NAME],[file_first_upload],[upload_date])
        VALUES('ThisValuesDoesntExists',1,DEFAULT);

SELECT * 
FROM TARGET_TABLE 

MERGE [dbo].[TARGET_TABLE] AS Target 
USING (VALUES ('ThisValuesDoesntExistss',1,GETDATE())) AS Source ([Name], [file_first_upload],[upload_date])
ON Target.[NAME] = Source.[Name] 
WHEN NOT MATCHED
    THEN INSERT ([NAME],[file_first_upload],[upload_date]) VALUES (Source.[Name], Source.file_First_upload, Source.upload_date);

SELECT * 
FROM TARGET_TABLE 

---

Related Topics