January 7, 2014 at 6:19 am
Hi,
I have a table with a field called SeqId which is not an identity nor a sequence but a kind of autonumber field (max(SeqId) + 1). Now I have to do a MERGE between 2 tables where the one with SeqId is the target.
How can I get the next SeqId for every row added? I tried this:
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T
USING dbo.seriesnacionales AS S
ON (T.RANGE1 = S.RANGOINI )
WHEN NOT MATCHED THEN
INSERT (SeqId, Range1, Range2, OPERATORCODE, NRN, StartDate, CreateDate)
VALUES((SELECT dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()), S.RangoIni, S.RangoFin, S.IdOperador,
'TEST_M', Convert(DATETIME, FECASIGNA , 103), SYSDATETIME())
WHEN MATCHED THEN
UPDATE SET T.Range1 = S.RangoIni, T.Range2 = S.RangoFin, T.OPERATORCODE = S.IdOperador
OUTPUT $action, Inserted.*, Deleted.*;
where the function just return Max(SeqId) + 1 but I always get the same value for that field.
Thanks for any help.
Mauricio
January 7, 2014 at 7:04 am
Something like this:
DECLARE @NextSeqId INT
SET @NextSeqId = dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()
;WITH CTEseriesnacionales AS (
SELECT NextSeqId = @NextSeqId + (ROW_NUMBER() OVER(ORDER BY RangoIni)-1),
*
FROM dbo.seriesnacionales
)
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T
USING CTEseriesnacionales AS S
ON (T.RANGE1 = S.RANGOINI )
...
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
January 7, 2014 at 7:33 am
Thanks Chris, it worked fine. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply