Getting Identity-like Values in SQL Server MERGE INSERT

mergesql server

I would like to merge a table bar into another table foo, and while doing so I have to assign a unique value to a column messagenr. This is my attempt to do so (sql-server):

MERGE foo AS target 
using bar AS source 
ON target.message LIKE source.message 
WHEN NOT matched BY target THEN 
INSERT (messagenr, 
        language, 
         message) 
VALUES ((SELECT Max(messagenr) 
         FROM   foo) 
       + 1, 
       'EN', 
       source.message);

Of course it is not working. I get the very same value for messagenr for every new dataset inserted.
I cannot modify the table structure itself, so I have to work with the structure given.

How can I get a UNIQUE value for messagenr in every new dataset in the target table?

Best Answer

Let me set up next example:

CREATE TABLE foo(messagenr int, language varchar(10), message varchar(100));
CREATE TABLE bar(language varchar(10), message varchar(100));
GO

INSERT INTO foo VALUES 
(1, 'EN', 'M1'),
(2, 'EN', 'M2'),
(3, 'ES', 'M3');

INSERT INTO bar VALUES
('FR', 'M1FR'),
('CA', 'M1CA'),
('DE', 'M1DE');
GO

Since you can't increase messagenr value inside the transaction, and your MERGE statement only insert values, maybe you could use another approach.

You can use a CTE or a subquery to enumerate the returned rows in this way:

SELECT (SELECT MAX(messagenr) FROM foo) +
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
       bar.language, 
       bar.message
FROM   foo
JOIN   bar
ON     bar.message LIKE '%' + foo.message + '%'

RN | language | message
:- | :------- | :------
4  | FR       | M1FR   
5  | CA       | M1CA   
6  | DE       | M1DE

Now you can insert new values using the previous query:

;WITH x AS
(
    SELECT (SELECT MAX(messagenr) FROM foo) +
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
           bar.language, 
           bar.message
    FROM   foo
    JOIN   bar
    ON     bar.message LIKE '%' + foo.message + '%'
)
INSERT INTO foo (messagenr, language, message)
SELECT RN, 'EN', message
FROM   x;

This is the final result:

SELECT * FROM foo;

messagenr | language | message
--------: | :------- | :------
        1 | EN       | M1     
        2 | EN       | M2     
        3 | ES       | M3     
        4 | EN       | M1FR   
        5 | EN       | M1CA   
        6 | EN       | M1DE

dbfiddle here

Related Question