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

| improve this answer | |
  • 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
0
DROP TABLE IF EXISTS #foo
DROP TABLE IF EXISTS #bar

CREATE TABLE #foo (messagenr INT, language VARCHAR(10), message VARCHAR(100));
CREATE TABLE #bar (language VARCHAR(10), message VARCHAR(100));

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

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

;WITH

x as (
    SELECT messagenr AS messagenr, language, message FROM #foo
    UNION ALL
    SELECT NULL, language, message  from #bar
),

y as (
    SELECT
        IIF(messagenr IS NULL, ROW_NUMBER() OVER (ORDER BY (SELECT 0)), messagenr) AS messagenr, language, message
    from
        x
)

MERGE
    #foo AS TARGET 
using
    y AS SOURCE
ON
    TARGET.message LIKE SOURCE.message 

WHEN NOT MATCHED BY TARGET THEN
    INSERT (messagenr, language, message)
    VALUES (SOURCE.messagenr, SOURCE.language, SOURCE.message);

SELECT * FROM #foo

messagenr   language   message
----------- ---------- -------
1           EN         M1
2           EN         M2
3           ES         M3
4           FR         M1FR
5           CA         M1CA
6           DE         M1DE
| improve this answer | |

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.