November 25, 2014 at 1:44 pm
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
November 25, 2014 at 2:00 pm
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/
November 25, 2014 at 2:20 pm
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.
November 25, 2014 at 2:36 pm
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/
November 26, 2014 at 12:11 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2014 at 12:27 pm
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