UPSERT method for MS SQL Server

[De Vos Meaker]

Hi, 

I have recently had to make a custom UPSERT method with Microsoft SQL, using their suggested MERGE method here: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15 

Is it within the scope of the SQLAlchemy project to create an extension for this UPSERT method through the use of MS SQL's MERGE? 

My company and I are exploring the possibility of contributing this to the project and would like to know if this is inline with the project and would get accepted?

Best regards, 

De Vos

[Mike Bayer]

it is something that would ideally be available for the MS SQL and Oracle dialects.  We've gotten by implementing specific "upsert" constructs for SQLite, Postgresql and MySQL that are not "MERGE".  The thing is MERGE is SQL standard, although I'm not sure it's worth it to make "MERGE" a standard construct (meaning, it would be in sqlalchemy.sql) since it is so seldom used and I would guess MS SQL and Oracle have lots of quirky syntaxes in each (or maybe not).

over at https://en.wikipedia.org/wiki/Merge_(SQL)#Implementations we can see Oracle / MSSQL support the SQL standard implementation and MSSQL adds some extra syntaxes.

I think MERGE could be implemented for MSSQL and Oracle and it likely would need to be a sqlalchemy.sql.merge() construct since it is SQL standard.

for general guidance on how these get implemented I'd look at the commits for similar features in MySQL, Postgresql, SQLite.  For example here's sqlite: https://github.com/sqlalchemy/sqlalchemy/commit/89ddd0b8976ed695d239898a2a8e4ebf531537f2

things to look out for:

- I dont think merge() should try to also "work" on SQlite/Postgresql/Mysql , these DBs dont support the syntax and for those people need to use the dialect-specific insert() constructs

- docs for merge() have to be pretty clear this is not a generally supported construct by the most popular open source databases.  

- dialect specific documentation for MSSQL, Oracle should likely have a short section on "upsert" that indicates, "SQL Server supports SQL standard MERGE which sqlalchemy implements as sqlalchemy.sql.merge() (link to main docs)

- since MERGE is SQL standard it can have "suite tests" that are available for any potential backend, in https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/testing/suite/test_insert.py or maybe better testing/suite/test_merge.py

we do have a "dream" of there being an ORM method that internally makes use of the various "upsert" or MERGE methods, which is described at https://github.com/sqlalchemy/sqlalchemy/issues/5441 .  this would solve the long-standing problem that people want to use the ORM with merge() in an automated way.  **however**, we do support an "upsert" case with the ORM right now as well that is mostly practical, which can be seen at https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-postgresql-on-conflict-with-returning-to-return-upserted-orm-objects .

https://github.com/sqlalchemy/sqlalchemy/issues/6757 is also a general request for MERGE , there have been some others.  major blocker has been that people want to target multiple backends.   that issue can be reopened if you want to target on that.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/62d0358a-e27e-4b04-9e86-4badb2fe59a4n%40googlegroups.com.