Click here to monitor SSC
  • Av rating:
  • Total votes: 265
  • Total comments: 17
Robert Sheldon

The MERGE Statement in SQL Server 2008

27 August 2010

When the SQL MERGE statement was introduced in SQL Server 2008, it allowed database programmers to replace reams of messy code with something quick, simple and  maintainable. The MERGE syntax just takes a bit of explaining, and Rob Sheldon is, as always, on hand to explain with plenty of examples.

Starting with SQL Server 2008, you can use a MERGE statement to modify data in a target table based on data in a source table. The statement joins the target to the source by using a column common to both tables, such as a primary key. You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.

The MERGE statement supports several clauses that facilitate the different types of data modifications. In this article, I explain each of these clauses and provide examples that demonstrate how they work. I created the examples on a local instance of SQL Server 2008. To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

USE AdventureWorks2008

IF OBJECT_ID ('BookInventory', 'U') IS NOT NULL

DROP TABLE dbo.BookInventory;

 

CREATE TABLE dbo.BookInventory  -- target

(

  TitleID INT NOT NULL PRIMARY KEY,

  Title NVARCHAR(100) NOT NULL,

  Quantity INT NOT NULL

    CONSTRAINT Quantity_Default_1 DEFAULT 0

);

IF OBJECT_ID ('BookOrder', 'U') IS NOT NULL

DROP TABLE dbo.BookOrder;

 

CREATE TABLE dbo.BookOrder  -- source

(

  TitleID INT NOT NULL PRIMARY KEY,

  Title NVARCHAR(100) NOT NULL,

  Quantity INT NOT NULL

    CONSTRAINT Quantity_Default_2 DEFAULT 0

);

INSERT BookInventory VALUES

  (1, 'The Catcher in the Rye', 6),

  (2, 'Pride and Prejudice', 3),

  (3, 'The Great Gatsby', 0),

  (5, 'Jane Eyre', 0),

  (6, 'Catch 22', 0),

  (8, 'Slaughterhouse Five', 4);

INSERT BookOrder VALUES

  (1, 'The Catcher in the Rye', 3),

  (3, 'The Great Gatsby', 0),

  (4, 'Gone with the Wind', 4),

  (5, 'Jane Eyre', 5),

  (7, 'Age of Innocence', 8);

As you can see, the script creates and populates the BookInventory and BookOrder tables. The BookInventory table represents the books that are or were available at a fictional book retailer. If the Quantity value for a book is 0, then the book has sold out.

The BookOrder table shows those books for which an order has been placed and delivered. If the Quantity value for a book listed in this table is 0, then the book had been requested but not included with the delivery. The Quantity values of both tables when added together represent the company’s current inventory.

NOTE: I created the BookInventory and BookOrder tables in the AdventureWorks2008 sample database. You can create the tables in any SQL Server 2008 user database. Be sure to change the USE statement in the script above to accurately reflect the target database.

Implementing the WHEN MATCHED Clause

The first MERGE clause we’ll look at is WHEN MATCHED. You should use this clause when you want to update or delete rows in the target table that match rows in the source table. Rows are considered matching when the joined column values are the same.

For example, if the BookID value in the BookInventory table matches the BookID value in the BookOrder table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the BookInventory table (the target) to the BookOrder table (the source) and then use a WHEN MATCHED clause to update the Quantity column in the target table:

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity;

 

SELECT * FROM BookInventory;

As you can see, the statement begins with the MERGE keyword, followed by the name of the target table. Note that the table name should be qualified as necessary. Notice that I’ve also assigned an alias (bi) to the target table to make it easier to reference that table later in the statement.

The next line in the statement is the USING clause, which is made up of the USING keyword, followed by the source table (again, qualified as necessary). I’ve also assigned an alias (bo) to this table. I then used an ON clause to join the two tables, based on the TitleID value in each table (bi.TitleID = bo.TitleID).

After I specified the target and source tables as well as the join condition, I added a WHEN MATCHED clause. The clause includes the WHEN MATCHED keywords, followed by the THEN keyword, next the UPDATE keyword, and finally a SET subclause. In this case, the SET expression specifies that the new Quantity value in the target table should equal the sum of the Quantity values from both the target and source tables (bi.Quantity = bi.Quantity + bo.Quantity). This way, the new Quantity value in the target table will reflect the accurate number of books available for sale, that is, the number of books that were on hand plus the number that arrived with the recent order.

That’s all there is to creating a basic MATCH statement. I also included a SELECT statement so we can view the contents of the target table. The following results reflect the new values in the Quantity column, as they appear in the BookInventory table after it has been updated with the MERGE statement:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

3

The Great Gatsby

0

5

Jane Eyre

5

6

Catch 22

0

8

Slaughterhouse Five

4

As the query results indicate, several of the rows have been updated to reflect the total inventory, based on the amounts in both the target and source tables. For example, the row in the BookInventory table with the TitleID value of 1 (The Catcher in the Rye) originally showed three books in stock. However, according to the BookOrder table, six more books were ordered, giving the company a total of nine books. As you would expect, the Quantity value in the BookInventory table is now 9.

You might have noticed that the book with the BookID value of 3 (The Great Gatsby) originally had a Quantity value of 0 in both the source and target tables. Suppose you want to remove from the BookInventory table any book whose Quantity value is 0 in both the target and source tables. You can easily delete such rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown in the following example:

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity;

 

SELECT * FROM BookInventory;

Notice that the new WHEN MATCHED clause includes a specific search condition after the AND keyword (bi.Quantity + bo.Quantity = 0). As a result, whenever rows from the two tables match and the two Quantity columns from the matched rows add up to 0, the row will be deleted, indicated by the DELETE keyword. Now the SELECT statement returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

5

Jane Eyre

5

6

Catch 22

0

8

Slaughterhouse Five

4

As you can see, the book The Great Gatsby has been removed from the inventory. You should note, however, that a MERGE statement can include at most only two WHEN MATCHED clauses. Whenever you do include two of these clauses, the first clause must include the AND keyword, followed by a search condition, as I’ve done here. The second WHEN MATCHED clause is then applied only if the first one is not.

NOTE: The examples in this article are independent of one another. That is, for each example you run, you should first rerun the table creation script if you want your results to match the ones shown here.

Implementing the WHEN NOT MATCHED [BY TARGET] Clause

The next clause in the MERGE statement we’ll review is WHEN NOT MATCHED [BY TARGET]. (The BY TARGET keywords are optional.) You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity

WHEN NOT MATCHED BY TARGET THEN

  INSERT (TitleID, Title, Quantity)

  VALUES (bo.TitleID, bo.Title,bo.Quantity);

 

SELECT * FROM BookInventory;

As the statement shows, I first specify the WHEN NOT MATCHED BY TARGET keywords, followed by the THEN keyword, and finally followed by the INSERT clause. The INSERT clause has two parts: the INSERT subclause and the VALUES subclause. You specify the target columns in the INSERT subclause and the source values in the VALUES clause. Notice that, for the VALUES subclause, I must qualify the column names with the table alias. The SELECT statement now returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

6

Catch 22

0

7

Age of Innocence

8

8

Slaughterhouse Five

4

Two new rows have been added to the BookInventory table: one for Gone with the Wind and one for Age of Innocence. Because the books existed in the source table, but not in the target table, they were inserted into the BookInventory table.

Implementing the WHEN NOT MATCHED BY SOURCE Clause

As you’ll recall from the discussion about the WHEN MATCHED clause, you can use that clause to delete rows from the target table. However, you can delete a row that matches a row in the source table. But suppose you want to delete a row from the target table that does not match a row in the source table.

For example, one of the rows originally inserted into the BookInventory table is for the book Catch 22. The Quantity value for that book was never updated because no order was placed for the book, that is, the book was never added to the BookOrder table. Because there are no copies of that book in stock, you might decide to delete that book from the target table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.

NOTE: Like the WHEN MATCHED clause, you can include up to two WHEN NOT MATCHED BY SOURCE clauses in your MERGE statement. If you include two, the first clause must include the AND keyword followed by a search condition.

The following example includes a WHEN NOT MATCHED BY SOURCE clause that specifies that any rows with a quantity of 0 that do not match the source should be deleted:

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity

WHEN NOT MATCHED BY TARGET THEN

  INSERT (TitleID, Title, Quantity)

  VALUES (bo.TitleID, bo.Title,bo.Quantity)

WHEN NOT MATCHED BY SOURCE

  AND bi.Quantity = 0 THEN

  DELETE;

 

SELECT * FROM BookInventory;

After I specified the WHEN NOT MATCHED BY SOURCE keywords, I specified AND followed by a search condition (bi.Quantity = 0). I then added the THEN keyword, and next the DELETE keyword. The results returned by the SELECT statement are shown in the following table:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

7

Age of Innocence

8

8

Slaughterhouse Five

4

As you can see, the BookInventory table no longer includes the row for Catch 22. And because the three MERGE clauses have been used together, the BookInventory now reflects the exact number of books that are currently on hand, and no books are included that are not in stock.

Implementing the OUTPUT Clause

When SQL Server 2005 was released, it included support for the OUTPUT clause in several data modification language (DML) statements. The OUTPUT clause is also available in the MERGE statement. The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. When used with a MERGE statement, the clause provides you with a powerful tool for capturing the modified data for archiving, messaging, or application purposes.

NOTE: To learn more about the OUTPUT clause, see the article “Implementing the OUTPUT Clause in SQL Server 2008” (http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/).

In the following example, I use an OUTPUT clause to pass the outputted data to a variable named @MergeOutput:

DECLARE @MergeOutput TABLE

(

  ActionType NVARCHAR(10),

  DelTitleID INT,

  InsTitleID INT,

  DelTitle NVARCHAR(50),

  InsTitle NVARCHAR(50),

  DelQuantity INT,

  InsQuantity INT

);

 

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity

WHEN NOT MATCHED BY TARGET THEN

  INSERT (TitleID, Title, Quantity)

  VALUES (bo.TitleID, bo.Title,bo.Quantity)

WHEN NOT MATCHED BY SOURCE

  AND bi.Quantity = 0 THEN

  DELETE

OUTPUT

    $action,

    DELETED.TitleID,

    INSERTED.TitleID,

    DELETED.Title,

    INSERTED.Title,

    DELETED.Quantity,

    INSERTED.Quantity

  INTO @MergeOutput;

 

SELECT * FROM BookInventory;

 

SELECT * FROM @MergeOutput;

Notice that I first declare the @MergeOutput table variable. In the variable, I include a column for the action type plus three additional sets of column. Each set corresponds to the columns in the target table and includes a column that shows the deleted data and one that shows the inserted data. For example, the DelTitleID and InsTitleID columns correspond to the deleted and inserted values, respectively, in the target table.

The OUTPUT clause itself first specifies the built-in $action variable, which returns one of three nvarchar(10) values—INSERT, UPDATE, or DELETE. The variable is available only to the MERGE statement. I follow the variable with a set of column prefixes (DELETED and INSERTED) for each column in the target table. The column prefixes are followed by the name of the column they’re related to. For example, I include DELETED.TitleID and INSERTED.TitleID for the TitleID column in the target table. After I specify the column prefixes, I then include an INTO subclause, which specifies that the outputted values should be saved to the @MergeOutput variable.

After the OUTPUT clause, which is the last clause in my MERGE statement, I added a SELECT statement to retrieve the updated contents of the BookInventory table, as I’ve done in previous examples. The SELECT statement returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

7

Age of Innocence

8

8

Slaughterhouse Five

4

Notice that I also include a second SELECT statement in my example above. This statement retrieves the contents of the @MergeOutput variable. The query results are shown in the following table:

ActionType

DelTitleID

InsTitleID

DelTitle

InsTitle

DelQuantity

InsQuantity

UPDATE

1

1

The Catcher in the Rye

The Catcher in the Rye

6

9

DELETE

3

NULL

The Great Gatsby

NULL

0

NULL

INSERT

NULL

4

NULL

Gone with the Wind

NULL

4

UPDATE

5

5

Jane Eyre

Jane Eyre

0

5

DELETE

6

NULL

Catch 22

NULL

0

NULL

INSERT

NULL

7

NULL

Age of Innocence

NULL

8

The results show any actions that were taken on the target table. For instance, the row for the Great Gatsby indicates that this row was deleted from the BookInventory table. The DelTitleID, DelTitle, and DelQuantity columns show the values that were deleted. However, the InsTitleID, InsTitle, and InsQuantity columns all show null values. That’s because no data was inserted into the target table for that row. If a row had been inserted, the InsTitleID, InsTitle, and InsQuantity columns will show the inserted values, but the DelTitleID, DelTitle, and DelQuantity columns will show null values because nothing is deleted when an insert is performed. Any updated rows will have values in all columns.

As you can see, when you use the OUTPUT clause in conjunction with the MERGE statement’s other three clauses—WHEN MATCHED, WHEN NOT MATCHED [BY TARGET], and WHEN NOT MATCHED BY SOURCE—you can perform multiple DML operations and verify your data modifications easily and efficiently. A MERGE statement can simplify your code, improve performance, and reduce your development effort. For more information on the MERGE statement, see the topic “Inserting, Updating, and Deleting Data by Using MERGE” in SQL Server 2008 Books Online.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 265 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: WHEN NOT MATCHED [BY TARGET] Clause Pitfall
Posted by: EdH334 (not signed in)
Posted on: Wednesday, September 08, 2010 at 12:49 AM
Message: One potential pitfall with the WHEN NOT MATCHED [BY TARGET] clause is that it is treated like a RIGHT OUTER JOIN, and so you cannot add a filter to the source side, e.g.

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET AND bo.Quantity > 0
THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity);

will still insert records where bo.Quantity = 0.

To see this result in a select try:

SELECT *
FROM BookInventory bi
RIGHT OUTER JOIN BookOrder bo
ON bi.TitleID = bo.TitleID
AND bo.Quantity > 0

not perhaps the result that you expect, but remember that the right join means "select *everything* from *BookOrder* and only those records from BookInventory where bi.TitleID = bo.TitleID and bo.Quantity > 0". The bo.Quantity > 0 has no affect on BookOrder when BookInventory has no match.

Subject: Lack of execution plan
Posted by: Clement (view profile)
Posted on: Wednesday, September 08, 2010 at 2:21 AM
Message: It would be interesting to enrich the article (very well written for a developer standpoint) with execution plans and comparison of performance and profiler statistics between the merge method and the traditional method.

It is particularly important during an upgrade to convince developers to adopt the new Merge methodology.

Subject: Examples?
Posted by: Sean Fowler (view profile)
Posted on: Thursday, September 09, 2010 at 11:36 AM
Message: Hi; admittedly I've only scanned through the article but what I was looking for was a comparison of code doing it the traditional way against code using Merge.

You say it allows us to do away with reams of messy code, but illustrating that with some of the messy code would be a useful way to demonstrate the benefit.

I'm sure at some point I'll read the article properly and get the point :)

Subject: RE Examples?
Posted by: Dwaine (not signed in)
Posted on: Tuesday, September 14, 2010 at 2:06 PM
Message: Sean,
Essentially, for the equivalent of the last example, you'd have to perform an individual batch for each action.

update bi
set bi.qty =....
from bi join bo

then another pass

delete bi
from bi LOJ bo
where bo.key is null

then another...

insert bi
(bo columns)
from bi ROJ bo
where bi.key is null

etc....

while at the same time maintaining a table of affected rows if desired. MERGE is MUCH cleaner.

Subject: Performance issues?
Posted by: ChristianBahnsen (view profile)
Posted on: Thursday, September 16, 2010 at 7:26 AM
Message: I'm just getting started with the MERGE statement. During a SQL Lunch earlier in the week I posed the question to Andy Leonard whether the MERGE statement would be more efficient than the SSIS dataflow he was demonstrating; Andy hadn't used MERGE. There was some discussion and some of the attendees had heard rumors that MERGE doesn't scale well. The sentiment was MERGE might be appropriate for small operations but not for huge recordsets. What are your thoughts on this matter?

Subject: MERGE is set-based, right?
Posted by: ChristianBahnsen (view profile)
Posted on: Thursday, September 16, 2010 at 12:03 PM
Message: I was just experimenting with my first MERGE statement with a source containing about 2500 rows and a target containing about 225K rows. It took about 13 seconds to run, which is longer than I expected. That's what prompts the question: MERGE is set-based, right?

I assume it is.

On reflection I realize that it has to compare source and target on every row. My code is below. Does it do the UPDATE section before the INSERT (effectively two passes) or does it do everything on one pass?

Thanks for the timely article!

-------------------------

MERGE tblReferrals AS T
USING tblReferralsCopy AS S
ON (T.OrderNumber = S.OrderNumber)
WHEN MATCHED THEN
UPDATE
SET
T.OrderingDMIS = S.OrderingDMIS,
T.EarliestDateTime = S.EarliestDateTime,
T.OrderingClinic = S.OrderingClinic,
T.OrderingHCP = S.OrderingHCP,
T.RequestedHCP = S.RequestedHCP,
T.OutpatientClinic = S.OutpatientClinic,
T.ClinicSpeciality = S.ClinicSpeciality,
T.OrderingMEPR = S.OrderingMEPR,
T.Priority = S.Priority,
T.AncillaryProcedure = S.AncillaryProcedure,
T.ProvisionalDx = S.ProvisionalDx,
T.InOutPatient = S.InOutPatient,
T.ReviewDte2 = S.ReviewDte2,
T.Reviewer = S.Reviewer,
T.LastStatus = S.LastStatus,
T.AptRequestStatus = S.AptRequestStatus,
T.WhoEnteredConsult = S.WhoEnteredConsult,
T.DateEntered = S.DateEntered,
T.TimeEntered = S.TimeEntered,
T.ChangeStatus = S.ChangeStatus,
T.MedicareStatus = S.MedicareStatus,
T.PriorAuthStatus = S.PriorAuthStatus,
T.TreatmentStartDate = S.TreatmentStartDate,
T.TreatmentStopDate = S.TreatmentStopDate,
T.NumberOfVisitsAuthorized = S.NumberOfVisitsAuthorized,
T.ReferralNumber = S.ReferralNumber,
T.PatIEN = S.PatIEN,
T.Specialty = S.Specialty

WHEN NOT MATCHED BY TARGET THEN
INSERT (
OrderingDMIS,
EarliestDateTime,
OrderNumber,
OrderingClinic,
OrderingHCP,
RequestedHCP,
OutpatientClinic,
ClinicSpeciality,
OrderingMEPR,
Priority,
AncillaryProcedure,
ProvisionalDx,
InOutPatient,
ReviewDte2,
Reviewer,
LastStatus,
AptRequestStatus,
WhoEnteredConsult,
DateEntered,
TimeEntered,
ChangeStatus,
MedicareStatus,
PriorAuthStatus,
TreatmentStartDate,
TreatmentStopDate,
NumberOfVisitsAuthorized,
ReferralNumber,
PatIEN,
Specialty
)
VALUES (
S.OrderingDMIS,
S.EarliestDateTime,
S.OrderNumber,
S.OrderingClinic,
S.OrderingHCP,
S.RequestedHCP,
S.OutpatientClinic,
S.ClinicSpeciality,
S.OrderingMEPR,
S.Priority,
S.AncillaryProcedure,
S.ProvisionalDx,
S.InOutPatient,
S.ReviewDte2,
S.Reviewer,
S.LastStatus,
S.AptRequestStatus,
S.WhoEnteredConsult,
S.DateEntered,
S.TimeEntered,
S.ChangeStatus,
S.MedicareStatus,
S.PriorAuthStatus,
S.TreatmentStartDate,
S.TreatmentStopDate,
S.NumberOfVisitsAuthorized,
S.ReferralNumber,
S.PatIEN,
S.Specialty
);
SELECT * FROM tblReferrals;

Subject: couple of points
Posted by: Praze (not signed in)
Posted on: Friday, September 17, 2010 at 4:06 AM
Message: EdH334, probably the best way to filter the source is to use the clause WITH before the merge.

Something like:

WITH BookOrderFiltered (TitleID,Title,Quantity) AS (
SELECT TitleID,Title,Quantity
FROM BookOrder
WHERE Quantity > 0
)

MERGE BookInventory bi
USING BookOrderFiltered bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET AND bo.Quantity > 0
THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity);

In terms of performance the SSIS Team posted a very good article comparing the cost of the SCD Component in SSIS vs the MERGE statement, and the bottom line was that the MERGE statement was always faster (although somethimes it might be a bit of an overkill).

Link to the post below:

http://blogs.msdn.com/b/mattm/archive/2010/08/05/optimizing-the-slowly-changing-dimension-wizard.aspx

Subject: Thanks for gr8 contribution in knowledge...
Posted by: Lakhan (not signed in)
Posted on: Friday, September 17, 2010 at 4:11 AM
Message: Its great to complete the insert, update, delete at the same time in two tables.

This can be done more than two table, if yes please provide the source code as well explaination of it.


Subject: SCDs
Posted by: ChristianBahnsen (view profile)
Posted on: Friday, September 17, 2010 at 9:29 AM
Message: I'm just starting to learn about SCDs, too. I thought that was more of a SSAS\OLAP concept. Can the term SCD also be applied to scenarios where one is maintaining a data warehouse mirroring OLTP?

The code snippet I posted above will be used in a scenario where we are exporting source files from a mainframe and updating warehouse tables hourly that are on a SQL 2008 Server.


Subject: Getting error for Alias
Posted by: vikrant.a.more (view profile)
Posted on: Monday, December 12, 2011 at 8:41 AM
Message: hi i am using SQL SERVER 2008.
and when i run the code given in the artical.
i am getting follwing error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bi'.

can somebody please help me to get this

i am running the code using merge

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Subject: Getting error for Alias
Posted by: vikrant.a.more (view profile)
Posted on: Monday, December 12, 2011 at 8:45 AM
Message: hi i am using SQL SERVER 2008.
and when i run the code given in the artical.
i am getting follwing error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bi'.

can somebody please help me to get this

i am running the code using merge

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Subject: Getting error for Alias
Posted by: vikrant.a.more (view profile)
Posted on: Monday, December 12, 2011 at 8:47 AM
Message: hi i am using SQL SERVER 2008.
and when i run the code given in the artical.
i am getting follwing error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bi'.

can somebody please help me to get this

i am running the code using merge

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Subject: Getting error for Alias
Posted by: vikrant.a.more (view profile)
Posted on: Monday, December 12, 2011 at 9:10 AM
Message: hi i am using SQL SERVER 2008.
and when i run the code given in the artical.
i am getting follwing error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bi'.

can somebody please help me to get this

i am running the code using merge

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Subject: How can we insert Multiple rows for non matching rows in target
Posted by: jatinmaestro (view profile)
Posted on: Friday, April 06, 2012 at 5:24 AM
Message: I am not it is possible or not

WHEN NOT MATCHED BY TARGET THEN
INSERT ([Column1]
,[Column2]
,[Column3]
)

VALUES
(
{ SOURCE.[Column1]
,'HardCoded Value'
,'HardCoded Value'
}
,
{SOURCE.[Column1]
,'HardCoded Value'
,'HardCoded Value'
}
);

Subject: How can we insert Multiple rows for non matching rows in target
Posted by: jatinmaestro (view profile)
Posted on: Friday, April 06, 2012 at 5:32 AM
Message: I am not it is possible or not

WHEN NOT MATCHED BY TARGET THEN
INSERT ([Column1]
,[Column2]
,[Column3]
)

VALUES
(
{ SOURCE.[Column1]
,'HardCoded Value'
,'HardCoded Value'
}
,
{SOURCE.[Column1]
,'HardCoded Value'
,'HardCoded Value'
}
);

Subject: Second insert or update is not working on the souce
Posted by: tech.firoz (view profile)
Posted on: Monday, May 20, 2013 at 12:36 PM
Message: Hi I am new for this MERGE COMMAND.

WHEN I AM TRYING TO PERFORM THE INSERT/UPDATE ON SOURCE ON THE SAME MERGE STATEMENT GETTING ERROR.
KINDLY HELP ME THAT I AM DOING CORRECT OR NOT OR I AM MISSING ANY CONCEPT

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity)
--WHEN NOT MATCHED BY SOURCE THEN
--DELETE --able to perform
WHEN NOT MATCHED BY SOURCE -- but not able to perform
AND bi.Quantity = 0 THEN
UPDATE
SET bo.Quantity = bi.Quantity + bo.Quantity ;

Subject: Need Help
Posted by: karti_it (view profile)
Posted on: Tuesday, May 28, 2013 at 3:55 AM
Message: Could you pl help me for below issue

MERGE into level_of_approve la
USING
(SELECT series_name FROM info@demo where in_use ='Y') lb
ON (lb.series_name = la.series_name)
WHEN NOT MATCHED THEN
INSERT (la.series_name)
VALUES (lb.series_name)
WHEN NOT MATCHED BY SOURCE THEN
delete;

It saying "keyword is missing" could you please help me ASAP.

I have added AND condition also in both clause but no luck.

Thanks in advance.
Regards,
Karthick

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.