0

I am going to use MERGE to insert or update a table depending upon ehether it's exist or not. This is my query,

declare @t table
(
    id int,
    name varchar(10)
)
insert into @t values(1,'a')

MERGE INTO @t t1
USING (SELECT id FROM @t WHERE ID = 2) t2 ON (t1.id = t2.id)
WHEN MATCHED THEN
    UPDATE SET name = 'd', id = 3
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (2, 'b');

select * from @t;

The result is,

id  name
1   a

I think it should be,

id  name
1   a
2   b
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322

4 Answers4

3

You have your USING part slightly messed up, that's where to put what you want to match against (although in this case you're only using id)

declare @t table
(
    id int,
    name varchar(10)
)

insert into @t values(1,'a')

MERGE INTO @t t1
USING (SELECT 2, 'b') AS t2 (id, name) ON (t1.id = t2.id)
WHEN MATCHED THEN
    UPDATE SET name = 'd', id = 3
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (2, 'b');

select * from @t;
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
2

As Mikhail pointed out, your query in the USING clause doesn't contain any rows.

If you want to do an upsert, put the new data into the USING clause:

MERGE INTO @t t1
USING (SELECT 2 as id, 'b' as name) t2 ON (t1.id = t2.id) --This no longer has an artificial dependency on @t
WHEN MATCHED THEN
    UPDATE SET name = t2.name
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (t2.id, t2.name);
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Damien, I need a general solution. Actually I want to convert my traditional if not exist insert else update with Merge – Imran Qadir Baksh - Baloch Jul 03 '13 at 12:05
  • 3
    @user960567 - I'm not sure what you mean by a "general solution" - what I've posted is the general way to do this. You put the *source* data in the `USING` clause - whether it be literals, as shown above, or it's a query against some other table. If you want a better answer, please provide some actual sample data and expected results. The query I've shown does produce the result that you've asked for. – Damien_The_Unbeliever Jul 03 '13 at 12:08
  • Just 1 single question damien? Is there any benefit of putting the above statement in transaction(normally)? – Imran Qadir Baksh - Baloch Jul 03 '13 at 12:20
  • @user960567 - every DML statement runs inside a transaction - whether you explicitly open one or not. If you don't open one, SQL Server opens one automatically and, by default, will commit it if the statement completes without error. There's an alternative mode (called Implicit Transactions) where it will instead leave the transaction open when the statement completes, but that's not on by default. So if you just planned to surround the above code with a `BEGIN TRANSACTION` and a `COMMIT`, I'd say no - because SQL Server is already doing exactly that. – Damien_The_Unbeliever Jul 03 '13 at 12:23
  • Besides, if you're really inserting into a `@table variable`, there isn't a whole lot of meaning to your transaction... – Aaron Bertrand Jul 03 '13 at 12:27
  • AaronBertrand, Actually @table was demo. I am doing this in my physical tables. Thanks Damien – Imran Qadir Baksh - Baloch Jul 03 '13 at 12:34
1

This query won't return anything:

SELECT id FROM @t WHERE ID = 2

Because where is no rows in table with ID = 2, so there is nothing to merge into table.

Besides, in MATCHED clause you are updating a field ID on which you are joining table, i think, it's forbidden.

Mikhail
  • 1,540
  • 2
  • 13
  • 13
0

For each DML operations you have to commit (Marks the end of a successful the transaction)Then only you will be able to see the latest data

For example :

GO
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;
GO
Mariappan Subramanian
  • 9,527
  • 8
  • 32
  • 33