Breaking News: Grepper is joining You.com. Read the official announcement!

merge in sql

Add Answer

Cloudy Crayfish answered on July 16, 2020 Popularity 9/10 Helpfulness 2/10

answer merge in sql

related SQL Merge

related merge in sql

related merge command in sql

related sql merge statement

related sql merge

merge in sql

Comment

/* Selecting the Target and the Source */
MERGE PRODUCT_LIST AS TARGET
    USING UPDATE_LIST AS SOURCE
 
    /* 1. Performing the UPDATE operation */
 
    /* If the P_ID is same,
       check for change in P_NAME or P_PRICE */
    ON (TARGET.P_ID = SOURCE.P_ID)
    WHEN MATCHED
         AND TARGET.P_NAME <> SOURCE.P_NAME
         OR TARGET.P_PRICE <> SOURCE.P_PRICE
 
    /* Update the records in TARGET */
    THEN UPDATE
         SET TARGET.P_NAME = SOURCE.P_NAME,
         TARGET.P_PRICE = SOURCE.P_PRICE
      
    /* 2. Performing the INSERT operation */
 
    /* When no records are matched with TARGET table
       Then insert the records in the target table */
    WHEN NOT MATCHED BY TARGET
    THEN INSERT (P_ID, P_NAME, P_PRICE)         
         VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
 
    /* 3. Performing the DELETE operation */
 
    /* When no records are matched with SOURCE table
       Then delete the records from the target table */
    WHEN NOT MATCHED BY SOURCE
    THEN DELETE
 
/* END OF MERGE */

Popularity 9/10 Helpfulness 2/10 Language sql

Source: www.geeksforgeeks.org

Tags: merge sql

Link to this answer
Share Copy Link

Contributed on Aug 24 2022

Cloudy Crayfish

0 Answers Avg Quality 2/10

Closely Related Answers

SQL Merge

Comment

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;
Code language: SQL (Structured Query Language) (sql)

Popularity 9/10 Helpfulness 4/10 Language sql

Source: www.sqlservertutorial.net

Tags: sql

Link to this answer
Share Copy Link

Contributed on Feb 02 2022

Curious Crab

0 Answers Avg Quality 2/10

merge in sql

Comment

MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;
Code language: SQL (Structured Query Language) (sql)

Popularity 9/10 Helpfulness 4/10 Language sql

Source: www.sqlservertutorial.net

Tags: merge merg

Link to this answer
Share Copy Link

Contributed on Apr 20 2023

Nanu

0 Answers Avg Quality 2/10

merge command in sql

Comment

USE SqlShackMergeDemo
GO
    
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;

Popularity 9/10 Helpfulness 4/10 Language sql

Source: Grepper

Tags: command

Link to this answer
Share Copy Link

Contributed on Feb 16 2022

rohit

0 Answers Avg Quality 2/10

sql merge statement

Comment

MERGE LoginTypes T
        USING (SELECT 'System' as Description) S
        ON(S.Description = T.Description)
WHEN NOT MATCHED BY TARGET
    THEN INSERT(Description, CreatedTimestamp, LastUpdatedTimestamp)
VALUES('System', getdate(), getdate());

Popularity 10/10 Helpfulness 2/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Jul 16 2020

Frightened Finch

0 Answers Avg Quality 2/10

sql merge

Comment


-- SQL SERVER MERGE

-- https://www.sqlshack.com/understanding-the-sql-merge-statement/

	IF OBJECT_ID('TEMPDB..#SourceProducts') IS NOT NULL
	BEGIN	DROP TABLE #SourceProducts;	END
	
		IF OBJECT_ID('TEMPDB..#TargetProducts') IS NOT NULL
	BEGIN	DROP TABLE #TargetProducts;	END


CREATE TABLE #SourceProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
	ISDEPOT  BIT
)

    
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(1,'1-1',1)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(2,'1-0',1)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(3,'0-1',0)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(4,'0-0',0)
INSERT INTO #SourceProducts(ProductID,ProductName, ISDEPOT) VALUES(88,'inserted in source',0)



    
CREATE TABLE #TargetProducts(
    ProductID		INT,
    ProductName		VARCHAR(50),
	ISDEPOT  BIT
)

    
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(1,'1-1',1)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(2,'1-0',0)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(3,'0-1',1)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(4,'0-0',0)
INSERT INTO #TargetProducts(ProductID,ProductName, ISDEPOT) VALUES(99,'inserted in target',0)

----

		MERGE #TargetProducts AS TARGET
		USING 	(	SELECT ProductID,ProductName, ISDEPOT
					FROM #SourceProducts
				) AS [SOURCE]
		ON [Source].ProductID = Target.ProductID
	
		-- INSERT
		WHEN NOT MATCHED BY Target THEN
		    INSERT (ProductID,ProductName, ISDEPOT) 
		    VALUES (Source.ProductID,Source.ProductName, Source.ISDEPOT)
	
		   
	   -- UPDATE
		WHEN MATCHED THEN UPDATE SET
			TARGET.ISDEPOT = SOURCE.ISDEPOT
	
		-- UPDATE
		WHEN NOT MATCHED BY SOURCE
		THEN UPDATE SET
			TARGET.ISDEPOT = 1
		
		--DELETE
		--WHEN NOT MATCHED BY Source THEN
	    	--DELETE
	
		;  --semicolon
	
---
    
SELECT 'SourceProducts',* FROM #SourceProducts
SELECT 'TargetProducts',* FROM #TargetProducts

Popularity 8/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: sql sql-merge sql-me

Link to this answer
Share Copy Link

Contributed on Jun 14 2023

Perry Vries

0 Answers Avg Quality 2/10

merge in sql

Contents

More Related Answers

merge in sql

Closely Related Answers

SQL Merge

merge in sql

merge command in sql

sql merge statement

sql merge

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.