search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

Why is dbxUpsert not possible for SQL Server? #35

Closed TobiasTDV closed 10 months ago

TobiasTDV commented 10 months ago

Firstly, thank you very much for your excellent package. I use it almost every day at work. Above all, the upsert command makes working with databases easier and significantly more performant!

For this reason, I would like to ask whether it will be possible to extend the upsert function for Microsoft SQL Server in the future. The syntax behind SQL Server is much more difficult, as SQL Server does not recognise the "ON DUPLICATE KEY" command: https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update But the Stackoverflow question at least shows basic answers to write the Upsert() function in SQL and thus also ideas on how to solve this problem in R.

I am writing this issue to initiate a solution for SQL Server. Nevertheless, I think the development of this package is great!

ankane commented 10 months ago

Hi @TobiasTDV, added experimental support in the upsert-sql-server branch if you want to try it out.

TobiasTDV commented 10 months ago

@ankane Great! I have done a few tests and (almost) everything seems to work as desired. However, there is no upsert when datetime columns exist in the dataset. I then converted the datetime column to a character. After that the upsert worked. But this seems to be a separate issue that should be addressed in the future. (See https://github.com/ankane/dbx/issues/27)

The upsert also doesn't work for a data set with more than 1000 rows, as such large inputs are apparently not possible for Microsoft SQL Server. Error message:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

I therefore set the batch size to 500, which then worked.

Otherwise very nice programming. Please keep up the good work!

Can you already say when the upsert for Microsoft SQL Server will be added to the main branch and when to the official Cran repository?

ankane commented 10 months ago

Thanks @TobiasTDV. For datetimes with SQL Server, it should work if you use the DATETIME2 type. Also, fixed the 1000 row limit and merged in 9a10e1efe55538011f88055490c383d4a4fc67b2, but don't have a timeline for a CRAN release.

jjesusfilho commented 10 months ago

I just tried and it's working very well.

ankane commented 10 months ago

Thanks @jjesusfilho, just pushed a new release to CRAN (make take a little time to appear).