1

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?

3

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

  • IT should be SELECT RN, language, message FROM x; to get the correct result. Your final resultset does not meet the requirement because of the constant EN returned, right? – Magier Mar 23 '18 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.