MERGE (Transact-SQL)

MERGE (Transact-SQL 2008)

MERGE is new feature , it Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Examples

CREATE TABLE TABLE1
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE1
VALUES(1,'JAN')
INSERT INTO TABLE1
VALUES(2,'FEB')
INSERT INTO TABLE1
VALUES(3,'MAR')
INSERT INTO TABLE1
VALUES(4,'APR')
INSERT INTO TABLE1
VALUES(5,'MAY')
GO

CREATE TABLE TABLE2
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE2
VALUES(1,'JAN')
INSERT INTO TABLE2
VALUES(6,'JUNE')
INSERT INTO TABLE2
VALUES(7,'JULY')


select * from TABLE2
select * from TABLE1

MERGE TABLE1 AS t1
USING (SELECT Monthid,Month_Name FROM TABLE2) AS t2
ON t1.Monthid = t2.Monthid
WHEN MATCHED AND t1.Monthid > 8 THEN DELETE
WHEN MATCHED THEN UPDATE SET t1.Monthid = t2.Monthid
WHEN NOT MATCHED THEN
INSERT(Monthid,Month_Name)
VALUES(Monthid,Month_Name);
GO

MSDN:
Using MERGE to perform INSERT and UPDATE operations on a table in a single statement

A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement. The following example shows a stored procedure that contains both an INSERT statement and an UPDATE statement. The procedure is then modified to perform the equivalent operations by using a single MERGE statement.

Advertisements

Find All SQL Server Instance Running in Local Network

How to list all SQL Server Instance Running in Local Network:
Some time you need to see the List of other running SQL server on your network. It is so simple and easy to call from other application also.

See the example:

-- ######### Query
EXEC master..xp_cmdshell 'osql -L'

-- ############ Result

NULL
Servers:
    (local)
    MAIL_SERVER
    RAGHU
    RAGHU\SQLEXPRESS
    SERVER1
NULL

If you want to get the same list from Command prompt then:

C:\> osql -L

it will show you the same list.