CODEWITHSUNDEEP
sql-servert-sqlmerge
EasyGer
EasyGer

Reputation: 3

MERGE statement contain multipart identifiers

I have written the following merge instruction:

MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f010.*, ISNULL(land.iso2,0) iso2land 
       FROM dbo.f010
       LEFT JOIN dbo.land ON dbo.f010.firma = land.firma 
                          AND dbo.f010.land = dbo.land.nr 
                          AND dbo.land.unr = 0) AS source
      ON target.ref_table ='f010' AND target.ref_key = source.key_1 

WHEN MATCHED AND (target.name1 != source.name1 COLLATE Latin1_General_CS_AS OR
                  target.name2 != source.name2 COLLATE Latin1_General_CS_AS OR
                  target.name3 != source.name3 COLLATE Latin1_General_CS_AS OR
                  target.strasse != source.str COLLATE Latin1_General_CS_AS OR
                  target.plz != source.plz     COLLATE Latin1_General_CS_AS OR
                  target.stadt != source.ort   COLLATE Latin1_General_CS_AS OR 
                  target.land != source.iso2land)
THEN 
    UPDATE 
        SET target.name1 = source.name1,
            target.name2 = source.name2,
            target.name3 = source.name3,
            target.strasse = source.str,
            target.plz = source.plz,
            target.stadt = source.ort,
            target.land = source.iso2land,
            target.eusanktstatus = 0,
            target.hhupd = '${hhupd}',
            target.eusanktinfo = '',
            target.info = '',
            target.firstname = '',
            target.middlename = '',
            target.lastname = '',
            target.wholename = '',
            target.street = '',
            target.zipcode = '',
            target.city = '',
            target.country = ''
 
WHEN NOT MATCHED BY TARGET THEN
    INSERT (target.name1, target.name2, target.name3, target.strasse, target.plz, target.stadt, target.land, target.hhupd)
    VALUES (source.name1, source.name2, source.name3,
            source.str, source.plz, source.ort, source.iso2land, '${hhupd}')

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This one worked very well, so I wanted to create a second one.

MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f012.*, ISNULL(land.iso2,0) iso2land FROM dbo.f012
       INNER JOIN f010 ON  f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto 
       LEFT JOIN dbo.land on dbo.f012.firma = land.firma  AND dbo.land.unr = 0 )
 AS source
    ON target.ref_table ='f012' AND target.ref_key = source.key_1 

WHEN MATCHED AND (target.name1 != source.nachname    OR
                  target.name2 != source.vorname    OR
                  target.name3 != source.adr_zusatz    OR
                  target.strasse != source.str    OR
                  target.plz != f010.plz        OR
                  target.stadt != f010.ort      OR 
                  target.land != source.iso2land)
THEN 
    
    UPDATE SET 
    target.name1 = source.nachname,
    target.name2 = source.vorname,
    target.name3 = source.adr_zusatz,
    target.strasse = f010.str,
    target.plz =     f010.plz,
    target.stadt =   f010.ort,
    
    target.land = source.iso2land,
    
    target.eusanktstatus = 0,
    target.hhupd = '${hhupd}',
    target.eusanktinfo = '',
    target.info = '',
    target.firstname = '',
    target.middlename = '',
    target.lastname = '',
    target.wholename = '',
    target.street = '',
    target.zipcode = '',
    target.city = '',
    target.country = ''

WHEN NOT MATCHED BY TARGET THEN
    INSERT (target.name1, target.name2, target.name3,target.strasse,target.plz,target.stadt, target.land, target.hhupd)
    VALUES (source.nachname, source.vorname, source.adr_zusatz,f010.str,f010.plz,f010.ort,source.iso2land, '${hhupd}')
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Here comes the error message from SQL Server:

The insertion column list used in the MERGE statement must not contain multipart identifiers. Use one-part identifiers instead.

It clearly encloses the part with the f010 So that must be where the mistake lies. But when I look at the original update script, I have to use it that way, because of the column properties of the different tables. So here comes the original script:

UPDATE sperradressen 
SET Adress.name1 = f012.nachname,
    sperradressen.name2 = f012.vorname,
    sperradressen.name3 = f012.adr_zusatz,
    sperradressen.strasse = f010.str,
    sperradressen.plz = f010.plz,
    sperradressen.stadt = f010.ort,
    sperradressen.land = land.iso2,
    sperradressen.eusanktstatus = 0,
    sperradressen.hhupd = '${hhupd}',
    sperradressen.eusanktinfo = '',
    sperradressen.info = '',
    sperradressen.firstname = '',
    sperradressen.middlename = '',
    sperradressen.lastname = '',
    sperradressen.wholename = '',
    sperradressen.street = '',
    sperradressen.zipcode = '',
    sperradressen.city = '',
    sperradressen.country = ''
FROM f012
INNER JOIN f010 ON ( f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto )
INNER JOIN land ON ( f010.firma = land.firma AND f010.land = land.nr AND land.unr = 0 )
WHERE f012.firma = ${TBR} 
      AND f012.anr = 00000000
      AND f012.key_1 = sperradressen.key_1
      AND (sperradressen.name1 != f012.nachname
           OR sperradressen.name2 != f012.vorname
           OR sperradressen.name3 != f012.adr_zusatz
           OR sperradressen.strasse != f010.str
           OR sperradressen.plz != f010.plz
           OR sperradressen.stadt != f010.ort
           OR sperradressen.land != land.iso2);

Now how do I get the correct one-piece identifier.?

 target.strasse != source.str    OR
 target.plz != f010.plz          OR
 target.stadt != f010.ort        OR

This is red-underlined, but how to make it work ?

Upvotes: 0

Views: 738

Answers (2)

Wouter
Wouter

Reputation: 2976

In your INSERT statement you are specifying values from the f010 table. That's not allowed, you should add them to the SOURCE table.

Something like this:

MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f012.*, ISNULL(land.iso2,0) iso2land, f010.str,f010.plz,f010.ort 
       FROM dbo.f012
       INNER JOIN f010 ON  f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto 
       LEFT JOIN dbo.land on dbo.f012.firma = land.firma  AND dbo.land.unr = 0 )
 AS source
    ON target.ref_table ='f012' AND target.ref_key = source.key_1 

WHEN MATCHED AND (target.name1 != source.nachname    OR
                  target.name2 != source.vorname    OR
                  target.name3 != source.adr_zusatz    OR
                  target.strasse != source.str    OR
                  target.plz != f010.plz        OR
                  target.stadt != f010.ort      OR 
                  target.land != source.iso2land)
THEN 
    
    UPDATE SET 
    target.name1 = source.nachname,
    target.name2 = source.vorname,
    target.name3 = source.adr_zusatz,
    target.strasse = f010.str,
    target.plz =     f010.plz,
    target.stadt =   f010.ort,
    
    target.land = source.iso2land,
    
    target.eusanktstatus = 0,
    target.hhupd = '${hhupd}',
    target.eusanktinfo = '',
    target.info = '',
    target.firstname = '',
    target.middlename = '',
    target.lastname = '',
    target.wholename = '',
    target.street = '',
    target.zipcode = '',
    target.city = '',
    target.country = ''

WHEN NOT MATCHED BY TARGET THEN
    INSERT (name1, name2, name3,strasse,plz,stadt, land, hhupd)
    VALUES (source.nachname, source.vorname, source.adr_zusatz,source.str,source.plz,source.ort,source.iso2land, '${hhupd}')
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Upvotes: 1

Bill Jetzer
Bill Jetzer

Reputation: 1115

You can only insert into the target table, so the alias is redundant, and in fact SQL Server won't allow it. Change from this:

INSERT (target.name1, target.name2, target.name3...

to this:

INSERT (name1, name2, name3...

Upvotes: 1

Related Questions