Is there a way to convert SQL inserted.ID INTO into merge statement?

Question

We need to change the way we populate the two temporary tables so that we use a MERGE instead of INSERTED. Using a Merge we would be able to query both the inserted table and the table it comes from which would allow us to guarantee the ordinals line up correctly.

  INSERT dbo.Segment_1
  (
    Name,
    Element,
  )
  OUTPUT
    INSERTED.Segment_No
  INTO dbo.#Segment_Log_Into_Table
  SELECT
    @Name,
    LEFT(ISNULL(S.Formatted_Value, ''), 500)
  FROM dbo.#Segment_Log_Table AS SLT
  OUTER APPLY dbo.XYZFUNCION(SLT.Element, 'C') AS S
  ORDER BY
    SLT.Ordinal;

Structure:
Table1 - Segment_1(Name Varchar(500),Element varchar(500),Segment bigint) 
Table2 - #Segment_Log_Into_Table(ORDINAL INT IDENTITY, Segment_No bigint)
Table3- #Segment_Log_Table(ORDINAL INT IDENTITY, Segment_No bigint)

We store the data into two temporary tables where we join the tables together in another below query based on an Ordinal but in some situations ordinal are wrong (not every time).

It looks like the way we handle this situation of ordinal creation doesn't guarantee the order of the second table to be the same as the first and below query end up with wrong\weird combination of element.

  INSERT dbo.Segment_2
  (
    Name,
    Element_Ext
  )
  SELECT
    @Name,
    SUBSTRING(ISNULL(S.Formatted_Value, ''), 501, LEN(ISNULL(S.Formatted_Value, '')) - 500)
  FROM dbo.#Segment_Log_Table AS SLT
  JOIN dbo.#Segment_Log_Into_Table AS SLIT
    ON SLIT.Ordinal = SLT.Ordinal
  OUTER APPLY dbo.XYZFunction(SLT.Element, 'C') AS S
  WHERE LEN(SLT.Element) > 500
  ORDER BY
    SLT.Ordinal;

Above query returns wrong combinations

`MERGE` doesn't replace the `inserted` pseudo table; `MERGE` is a DML operator. — Thom A, Jul 29 '22 at 08:39
Would you be able to provide an example of the table data so that we can replicate this issue? — Andrew, Jul 29 '22 at 08:41
Does this help? [Is it possible to for SQL Output clause to return a column not being inserted?](https://stackoverflow.com/q/10949730/1048425) — GarethD, Jul 29 '22 at 08:44
@Andrew like I said issue can not be replicate, this ordinal order mismatch just happened once out of 10 times so This solution needed in case it should not happened in future. Let me know if data still needed and I will arrange one — user8758891, Jul 29 '22 at 08:45
Are you some how expecting that if you have 2 `IDENTITY` columns that when you `INSERT` data you can *rely* on them generating the same number? I really don't follow what you are asking here. Consumable sample data, expected results, (and a [mre]?) will likely help us understand what you want here. — Thom A, Jul 29 '22 at 08:49

score 0 · Accepted Answer · answered Jul 29 '22 at 08:51

0

The following is your first insert re-written as a quasi-merge (I say quasi because it doesn't actually merge it only ever inserts):

MERGE dbo.Segment_1 AS t
USING
(   SELECT  Name = @Name,
            Element = LEFT(ISNULL(S.Formatted_Value, ''), 500),
            SLT.Ordinal
    FROM    dbo.#Segment_Log_Table AS SLT
    OUTER APPLY dbo.XYZFUNCION(SLT.Element, 'C') AS S
) AS s
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT (Name, Element)
    VALUES (s.Name, s.Element)
OUTPUT Inserted.Segment_No, s.Ordinal 
    INTO dbo.#Segment_Log_Into_Table (Segment_No, Ordinal);

This means that you can capture the newly inserted data and the source ordinal in the output and retain the correct mapping between the two.

answered Jul 29 '22 at 08:51

GarethD

68,045
10
83
123

I replaced with above code not sure why its giving inentity error , "Cannot insert explicit value for identity column in table 'Segment_Log' when IDENTITY_INSERT is set to OFF." – user8758891 Jul 29 '22 at 10:26
Presumably `Segment_Log` is the table that you are using to capture the output, if so `ORDINAL` no longer needs to be an identity column, as you don't want to create a new identity, you want to capture the identify from the source table – GarethD Jul 29 '22 at 10:43
my bad , you are right I forgot to change structure of temp table – user8758891 Jul 29 '22 at 10:55
is there a way to use order by in source query as sequence is impacting if order by is not used and I can not use order by clause in inline source query ? – user8758891 Aug 01 '22 at 09:57
You shouldn't need to rely on the order of the insert, and if you do need to rely on it then you are likely doing something wrong. As Larnu has said, I think you need to create a minimal, reproducible example of your issue as I am really not following it so far – GarethD Aug 01 '22 at 10:10
I rely on the order because there are other sprocs which uses Segment_1 table which contains queries like Select * from Segment_1 S Join ..... order by S.Segment_No and this causes the sequence problem , I know we can't use here order by inside source query is there anything else we can tweak here – user8758891 Aug 01 '22 at 11:56

Is there a way to convert SQL inserted.ID INTO into merge statement?

1 Answers1