Equivalent for Merge with output clause in SQL Server to Postgresql

Question

I want to insert values based on below code to a temporary table in postgresql

declare @output table (AuditScratchID bigint, AuditID bigint);

merge table atb
 using (select 
            s.ID
            ....
            ....
            ....
        from @temporaryTableVariable s
            inner join ....
...............
..............

        ) as s
 on 1 = 2 -- Impossible Condition so they never match
 when not matched then
    insert (.....)
    values (.....)
    output s.ID, inserted.ID
    into @output;

Just to mention, how can I correlate values into temporary table

I don't understand what that MERGE is supposed to do if you force it to never match? Isn't that a simple insert into ... select from temp_table in the end? — user330315
– user330315, Commented Apr 14, 2020 at 8:14
It is, the op has even stated that with the comment on 1 = 2 -- Impossible Condition so they never match — Andronicus
– Andronicus, Commented Apr 14, 2020 at 8:21
@Andronicus: the question is: why? Why is this necessary in SQL Server instead of a simply insert into .. select from ...? — user330315
– user330315, Commented Apr 14, 2020 at 8:26
I am doing a migration from SQL Server to PostgreSQL. Client has such code written, hence i wanted to know how can i handle output clause and insert record in PostgreSQL — swapnil solanki
– swapnil solanki, Commented Apr 14, 2020 at 8:34
the reason you want to force an insert on a merge statement in MSSQL is because the OUTPUT clause of a merge statement in MSSQL has access to columns that do not exist in the target table. — chris costa
– chris costa, Commented May 27, 2024 at 21:26

user330315 · Accepted Answer · 2020-04-14 08:16:10Z

4

I don't understand the use of MERGE to begin with.

This seems like a straightforward insert ... select. To see the inserted rows, use the returning clause

insert into atb (...)
select ... columns ...
from some_table
  join other_table on ...
returning *

answered Apr 14, 2020 at 8:16

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Equivalent for Merge with output clause in SQL Server to Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related