Update/Insert in one query

  • I have been reading up on Merge, but I don't think it is possible with what I am trying to do. I have a view that is my source and based on a recid table will determine if I need to update or insert into a table. If I need to insert, it will take the last recid and increment it by 1 for each row inserted on the fly as this is a primary key column.

    Thanks in advance for any guidance.

    If there is an insert, I would need to do something like the following to set the next recid for each row inserted:

    SET @RecID = COALESCE((SELECT nextval FROM dbo.systemsequences WHERE nextval = @recid), 0) + 1;

    What would be the best way to do this if Merge is not an option? In Oracle I would do something like the below, but not sure how to do this with TSQL:

    --Pass in v_dataareaid

    -- Get Cursor Insert/Update Variables

    SELECT fiscalcalendaryear_endyear, fiscalcalendarperiod_periodid, fiscalcalendarperiod_enddate

    INTO v_fiscalcalendaryear_endyear,

    v_fiscalcalendarperiod_periodid,

    v_fiscalcalendarperiod_enddate

    FROM dbo.sfcfiscalperiods_noaot

    WHERE dataarea = v_dataareaid

    AND fiscalcalendarperiod_startdate = dateadd (mm, datediff (mm, 0, getdate ()) - 1, 0);

    -- Cursor

    SELECT inventdim.inventsiteid,

    inventdim.inventlocationid,

    inventdim.wmslocationid,

    inventdim.inventcolorid,

    sfciteminventorytransactionsdaily.itemid,

    sum (sfciteminventorytransactionsdaily.unposted_physical_qty) unposted_physical_qty,

    sum (sfciteminventorytransactionsdaily.unposted_physical_amt) unposted_physical_amt,

    sum (sfciteminventorytransactionsdaily.posted_physical_qty) posted_physical_qty,

    sum (sfciteminventorytransactionsdaily.posted_physical_amt) posted_physical_amt,

    sum (sfciteminventorytransactionsdaily.total_physical_qty) total_physical_qty,

    sum (sfciteminventorytransactionsdaily.total_physical_amt) total_physical_amt

    FROM dbo.sfciteminventorytransactionsdaily_noaot sfciteminventorytransactionsdaily

    JOIN

    dbo.inventdim

    ON (sfciteminventorytransactionsdaily.dataareaid = inventdim.dataareaid

    AND sfciteminventorytransactionsdaily.inventdimid = inventdim.inventdimid)

    WHERE sfciteminventorytransactionsdaily.dataareaid = v_dataareaid

    AND sfciteminventorytransactionsdaily.datephysical <= v_fiscalcalendarperiod_enddate

    GROUP BY inventdim.dataareaid,

    inventdim.inventsiteid,

    inventdim.inventlocationid,

    inventdim.wmslocationid,

    inventdim.inventcolorid,

    sfciteminventorytransactionsdaily.itemid;

    -- For Each Row

    UPDATE dbo.sfcdairyunitrunreport

    SET unpostedphysicalqty = cursor_row.unpostedphysicalqty,

    unpostedphysicalamount = cursor_row.unpostedphysicalamount,

    postedphysicalamount = cursor_row.postedphysicalamount,

    postedphysicalqty = cursor_row.postedphysicalqty,

    totalphysicalqty = cursor_row.totalphysicalqty,

    totalphysicalamount = cursor_row.totalphysicalamount

    WHERE dataareaid = v_dataareaid

    AND inventsiteid = cursor_row.inventsiteid

    AND inventlocationid = cursor_row.inventlocationid

    AND wmslocationid = cursor_row.wmslocationid

    AND inventcolorid = cursor_row.inventcolorid

    AND itemid = cursor_row.itemid

    AND fiscalyearend = v_fiscalcalendaryear_endyear

    AND fiscalperiod = v_fiscalcalendarperiod_periodid;

    -- Did you update something? If Not Insert

    IF SQL%ROWCOUNT = 0

    THEN

    -- Get a Recid

    INSERT INTO dbo.sfcdairyunitrunreport (dataareaid,

    recversion,

    recid,

    inventsiteid,

    inventlocationid,

    wmslocationid,

    inventcolorid,

    itemid,

    fiscalyearend,

    fiscalperiod,

    unpostedphysicalqty,

    postedphysicalqty,

    totalphysicalqty,

    unpostedphysicalamount,

    postedphysicalamount,

    totalphysicalamount)

    VALUES (v_dataareaid,

    v_recversion,

    v_recid,

    cursor_row.inventsiteid,

    cursor_row.inventlocationid,

    cursor_row.wmslocationid,

    cursor_row.inventcolorid,

    cursor_row.itemid,

    v_fiscalyearend,

    v_fiscalperiod,

    cursor_row.unpostedphysicalqty,

    cursor_row.postedphysicalqty,

    cursor_row.totalphysicalqty,

    cursor_row.unpostedphysicalamount,

    cursor_row.postedphysicalamount,

    cursor_row.totalphysicalamount);

    END IF; -- did you update?

    -- END LOOP FOR each Row

    --END

    Once done I would then update the table that holds the next value to be used for the recid:

    update systemsequences set nextval = a.nextval

    from

    (

    select max(s.recid)+1 as nextval from SFCDAIRYUNITRUNREPORT s

    ) a

    where tabid = '102891'

    GO

  • MERGE has several bugs. I would avoid it. That being said it is hard to figure out exactly what you are trying to do. Could you just an identity column and not worry about trying to maintain the "next" value? Trying to maintain a running sequence is fraught with issues. It is nearly impossible to get it right because of concurrency issues. In short, don't do this. Either use an identity or a sequence. You should be able to do this in two queries pretty easily. One does the inserts, the other the updates.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I have to keep the nextval due to our ERP system. This will be a job that runs once a night, so there would be no concurrency issue. All I am trying to do is determine if the records exists. If it does then update else insert the new rows.

  • Cheryl McLaughlin-385812 (11/25/2014)


    I have to keep the nextval due to our ERP system. This will be a job that runs once a night, so there would be no concurrency issue. All I am trying to do is determine if the records exists. If it does then update else insert the new rows.

    OK so break this into 2 parts. I can't even begin to decipher the oracle code posted. Between the unfamilar syntax and the lack of capitalization in column names it all looks the same. Just a suggestion but "sfciteminventorytransactionsdaily" looks like a big string of gibberish. Just adding some capitalization makes it a LOT easier to decipher. "sfcItemInventoryTransactionsDaily"

    So the first part would be to do an update where exists, followed an insert where not exists.

    For the insert you could use a numbers/tally table and get all the "new" values at once instead of row by row. http://www.sqlservercentral.com/articles/62867/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • It would be useful to know a bit more about the dbo.systemsequences table.

    Does this hold 'next id for table x' values only, or is there more to it than that? If you determine (in code) that 50 inserts are required, can you just update this number to (PreviousValue + 50) and then do all of the inserts in one hit, using a tally table along the lines already suggested by Sean?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Here is an article that talks about the issues with MERGE and lists the connect items associated with it.

    http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 6 posts - 1 through 5 (of 5 total)

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