mercredi 7 octobre 2015

MsSql Merge not working

I'm trying to preform a merge but for some reason i get a error.

What I'm doing is: I am insert all the records where the productname is not empty and it is not marked as double in a temp table.

After I loop through temp table to insert/update every record in the real table [Products]

MERGE INTO [Products] AS T
USING SingleProducts AS S
ON (T.SoftwareID = S.[@ProductCode] AND T.PriceType = S.[@PriceType])
WHEN NOT MATCHED BY TARGET
    THEN INSERT (SoftwareID, IsActive, [Image], ProductNameNL, ProductNameFR, DescriptionNL, DescriptionFR, UnitPrice, PriceType, CreationDate, LastUpdatedDate)
        VALUES (S.[@ProductCode], 0, 'no-image.png', S.[@ProductNameNL], S.[@ProductNameFR], S.[@DescriptionNL], S.[@DescriptionFR], 
                    S.[@UnitPrice], S.[@PriceType], S.[@CreationDate], GETDATE())
WHEN MATCHED
    THEN UPDATE SET
        T.[SoftwareID] = S.[@ProductCode],
        T.[ProductNameNL] = S.[@ProductNameNL],
        T.[ProductNameFR] = S.[@ProductNameFR],
        T.[DescriptionNL] = S.[@DescriptionNL],
        T.[DescriptionFR] = S.[@DescriptionFR],
        T.[UnitPrice] = S.[@UnitPrice],
        T.[PriceType] = S.[@PriceType]
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, inserted.*, deleted.*;

Aucun commentaire:

Enregistrer un commentaire