Multiple DMLS in one when clause of MERGE Statement

  • Hi All,

    I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But I am getting problem while executing this code .Please provide a better way to do it.

    MY CODE :

    Merge Vacancy.TB_FTSearchData as A

    using vacancy.TB_Vacancy as B

    on A.FTSearchVacancyInternalID=B.VacancyInternalID and A.FTSearchContractID=B.VacancyContractID

    When not Matched by source then Delete

    When not Matched by target and B.VacancyFTSupdated=1 and B.VacancyStatusID=1 then

    INSERT ([FTSearchVacancyInternalID]

    ,[FTSearchVacancyTitle]

    ,[FTSearchVacancyCleanDescription]

    ,[FTSearchVacancyCategoryName]

    ,[FTSearchVacancyTagName]

    ,[FTSearchContractID]

    ,[FTSearchVacancyLCID]

    ,[CreatedDate]

    ,[ModifiedDate])

    Values (VacancyInternalID, VacancyTitle, VacancyCleanDescription, dbo.fn_GetCSV(B.VacancyInternalID,1) ,

    dbo.fn_GetCSV(B.VacancyInternalID,2) , VacancyContractID,(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID), GETDATE(), NULL )

    UPDATE

    SET B.VacancyFTSupdated=3

    WHERE VacancyFTSupdated=1 and VacancyStatusID=1 and VacancyInternalID in (Select FTSearchVacancyInternalID from Vacancy.TB_FTSearchData)

    when matched and B.VacancyFTSupdated=2 AND B.VacancyStatusID=1 then

    UPDATE

    SET

    A.FTSearchVacancyTitle=B.VacancyTitle

    ,A.FTSearchVacancyCleanDescription=VacancyCleanDescription

    ,A.FTSearchVacancyCategoryName=dbo.fn_GetCSV(B.VacancyInternalID,1)

    ,A.FTSearchVacancyTagName=dbo.fn_GetCSV(B.VacancyInternalID,2)

    ,[FTSearchContractID]=VacancyContractID

    ,[FTSearchVacancyLCID]=(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID)

    ,[ModifiedDate]=GETDATE()

    OUTPUT $action,Deleted.*,Inserted.* ;

  • Please express any schemas that need to be created.

    Please post the two tables you are referring to as CREATE statements.

    Please supply the insert statements to populate the before mentioned tables.

    John Miner
    Crafty DBA
    www.craftydba.com

  • The Create table statements are

    CREATE TABLE [Vacancy].[TB_Vacancy](

    [VacancyInternalID] [bigint] IDENTITY(1,1) NOT NULL,

    [VacancyExternalID] [nvarchar](40) NULL,

    [VacancyReferenceNumber] [nvarchar](100) NULL,

    [VacancyTitle] [nvarchar](400) NOT NULL,

    [VacancyQuantity] [smallint] NULL,

    [VacancyYearsOfExperience] [tinyint] NULL,

    [VacancyCompetency] [nvarchar](max) NULL,

    [VacancySalaryBaseRate] [numeric](14, 0) NULL,

    [VacancySalaryTopRate] [numeric](14, 0) NULL,

    [VacancySalaryFrequencyID] [int] NULL,

    [VacancyBenefits] [nvarchar](max) NULL,

    [VacancyStartDate] [datetime2](0) NULL,

    [VacancyEndDate] [datetime2](0) NULL,

    [VacancyRequirements] [nvarchar](max) NULL,

    [VacancyDescription] [nvarchar](max) NULL,

    [VacancyDescriptionAbstract] [nvarchar](1000) NULL,

    [VacancyCleanDescription] [nvarchar](max) NULL,

    [VacancyCleanDescriptionAbstract] [nvarchar](1000) NULL,

    [VacancyApplyURI] [varchar](2000) NULL,

    [VacancyApplicationCloseDate] [datetime2](0) NULL,

    [VacancyPostingStartDate] [datetime2](0) NULL,

    [VacancyPostingEndDate] [datetime2](0) NULL,

    [VacancyContactID] [int] NULL,

    [VacancyLongitude] [numeric](14, 10) NULL,

    [VacancyLatitude] [numeric](14, 10) NULL,

    [VacancyGeoLocation] [geography] NULL,

    [VacancyLocationAccuracy] [tinyint] NULL,

    [VacancyAddressLine1] [nvarchar](200) NULL,

    [VacancyAddressLine2] [nvarchar](200) NULL,

    [VacancyAddressLine3] [nvarchar](200) NULL,

    [VacancyPostCode] [nvarchar](20) NULL,

    [VacancyCityID] [int] NULL,

    [VacancyProvinceID] [int] NULL,

    [VacancyCountryID] [int] NULL,

    [VacancyRegionID] [int] NULL,

    [VacancyLanguageID] [int] NOT NULL,

    [VacancyCurrencyID] [int] NULL,

    [VacancyAdditionalInformation] [xml] NULL,

    [VacancyClientInternalID] [int] NULL,

    [VacancyStatusID] [tinyint] NOT NULL,

    [VacancyContractTypeID] [int] NULL,

    [VacancyShiftID] [int] NULL,

    [VacancyIndustryID] [int] NULL,

    [VacancyPostingPersonContactID] [int] NULL,

    [VacancyContractID] [int] NOT NULL,

    [VacancyBrandID] [int] NULL,

    [VacancyBusinessUnitID] [int] NULL,

    [VacancyBranchID] [int] NULL,

    [CreatedBy] [nvarchar](100) NOT NULL,

    [CreatedDate] [datetime2](0) NOT NULL,

    [ModifiedBy] [nvarchar](100) NULL,

    [ModifiedDate] [datetime2](0) NULL,

    [DeletedBy] [nvarchar](100) NULL,

    [DeletedDate] [datetime2](0) NULL,

    [VacancyFTSupdated] [tinyint] NOT NULL,

    [VacancyAnnualSalaryBaseRate] [numeric](14, 0) NULL,

    [VacancyAnnualSalaryTopRate] [numeric](14, 0) NULL,

    CONSTRAINT [PK_VacancyInternalID] PRIMARY KEY CLUSTERED

    (

    [VacancyInternalID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    CREATE TABLE [Vacancy].[TB_FTSearchData](

    [FTSearchVacancyInternalID] [int] NOT NULL,

    [FTSearchVacancyTitle] [nvarchar](400) NOT NULL,

    [FTSearchVacancyCleanDescription] [nvarchar](max) NULL,

    [FTSearchVacancyCategoryName] [nvarchar](4000) NULL,

    [FTSearchVacancyTagName] [nvarchar](4000) NULL,

    [FTSearchContractID] [int] NOT NULL,

    [FTSearchVacancyLCID] [int] NOT NULL,

    [CreatedDate] [datetime2](0) NOT NULL,

    [ModifiedDate] [datetime2](0) NULL,

    CONSTRAINT [PK_FTSearch_VacancyInternalID] PRIMARY KEY CLUSTERED

    (

    [FTSearchVacancyInternalID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • 28.kanikasoni (4/12/2013)


    Hi All,

    I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But I am getting problem while executing this code .Please provide a better way to do it.....

    Can you describe the problems?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • MERGE Vacancy.TB_FTSearchData as t -- target

    USING vacancy.TB_Vacancy as s -- source

    ON t.FTSearchVacancyInternalID = s.VacancyInternalID

    AND t.FTSearchContractID = s.VacancyContractID

    WHEN not Matched by source THEN DELETE

    WHEN not Matched by target and s.VacancyFTSupdated = 1 and s.VacancyStatusID = 1 THEN

    INSERT (

    [FTSearchVacancyInternalID]

    ,[FTSearchVacancyTitle]

    ,[FTSearchVacancyCleanDescription]

    ,[FTSearchVacancyCategoryName]

    ,[FTSearchVacancyTagName]

    ,[FTSearchContractID]

    ,[FTSearchVacancyLCID]

    ,[CreatedDate]

    ,[ModifiedDate])

    VALUES (

    s.VacancyInternalID,

    s.VacancyTitle,

    s.VacancyCleanDescription,

    dbo.fn_GetCSV(s.VacancyInternalID,1) ,

    dbo.fn_GetCSV(s.VacancyInternalID,2) ,

    s.VacancyContractID,

    (Select la.LCID

    from ReferenceData.TB_Language la

    where la.languageInternalID = s.vacancyLanguageID),

    GETDATE(),

    NULL )

    -- second statement not permitted, you could use the OUTPUT for this

    /*

    UPDATE

    SET B.VacancyFTSupdated = 3

    WHERE VacancyFTSupdated = 1

    and VacancyStatusID = 1

    and VacancyInternalID in (Select FTSearchVacancyInternalID from Vacancy.TB_FTSearchData)

    */

    when matched and s.VacancyFTSupdated = 2 AND s.VacancyStatusID = 1 then

    UPDATE

    SET

    FTSearchVacancyTitle= s.VacancyTitle

    ,FTSearchVacancyCleanDescription= s.VacancyCleanDescription

    ,FTSearchVacancyCategoryName= dbo.fn_GetCSV(s.VacancyInternalID,1)

    ,FTSearchVacancyTagName= dbo.fn_GetCSV(s.VacancyInternalID,2)

    ,[FTSearchContractID]= s.VacancyContractID

    ,[FTSearchVacancyLCID]= (

    Select la.LCID

    from ReferenceData.TB_Language la

    where la.languageInternalID = s.vacancyLanguageID)

    ,[ModifiedDate]= GETDATE()

    OUTPUT $action, Deleted.*, Inserted.* ;

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I want to use 2 statements in case of when matched clause

    i.e

    Update all the values of Vacancy.TB_FTSearchData

    also

    update the VacancyFTSupdated from table vacancy.TB_Vacancy

  • I want this, I want that - no gratitude?

    https://sqlroadie.com/

  • I don't believe you can perform multiple DMLs from the MERGE.

    You can however perform an insert from the output of a merge.

    From msdn OUTPUT CLAUSE example K.

    "INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)

    SELECT ProductID, GETDATE()

    FROM

    ( MERGE Production.ProductInventory AS pi

    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

    JOIN Sales.SalesOrderHeader AS soh

    ON sod.SalesOrderID = soh.SalesOrderID

    AND soh.OrderDate = '20070401'

    GROUP BY ProductID) AS src (ProductID, OrderQty)

    ON (pi.ProductID = src.ProductID)

    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

    THEN DELETE

    WHEN MATCHED

    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)

    WHERE Action = 'DELETE'; "

  • 28.kanikasoni (4/17/2013)


    I want to use 2 statements in case of when matched clause

    i.e

    Update all the values of Vacancy.TB_FTSearchData

    also

    update the VacancyFTSupdated from table vacancy.TB_Vacancy

    The simple answer is - you can't. Not with SQL Server. But as has already pointed out, you could use your captured output.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks ChrisM@Work ,will try to use output data.

    My thought:

    If you want something, then you can achieve it. You will get all you want in life if you help enough other people get what they want.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply