Comments (9)
Just out of curiosity, what kind of environment might have a limitation like this?
from generate-sql-merge.
It's a SQL Server on the client's side of things, I don't know much about it.
from generate-sql-merge.
Ah ok, so something completely non-standard/artificial. Thanks.
from generate-sql-merge.
I have come across a similar problem as well.
Our system does not have an explicit limit on the maximum, but they system ran out of resource before completing the script
I found that commenting out the following enables the script to successfully finish.
WHEN NOT MATCHED BY SOURCE THEN
DELETE
from generate-sql-merge.
@dshin198, yes the script would take much more resource if you use WHEN NOT MATCHED BY SOURCE, I saw you created an issue mentioning 1.7m records, it will definitely not work. The query generated is purely putting things into memory table. I have a pull request that nobody seem to care provided a work around that will insert those source into a temp table first and then perform the merge, therefore removed the heavy resource usage on the merge itself. But I never tried a table with million records. In your extreme case I'm afraid MERGE itself won't work well for you, If you google around you can see all sort of problems about MERGE, performance is one of the problem.
from generate-sql-merge.
@simonwangu I see. It would be very nice to have your implementation as the default or at least an option available by using flag.
Thanks for your advice about the MERGE comment in general as well. I'll share your information with my team.
from generate-sql-merge.
@deyshin wrote:
@simonwangu It would be very nice to have your implementation as the default or at least an option available by using flag.
The WHEN NOT MATCHED BY SOURCE THEN DELETE
clause can be excluded by specifying the following parameter: @delete_if_not_matched = 0
.
Given many users of the tool use it to generate scripts to synchronise static data as part of an automated deployment process, and therefore rely on it to perfectly match the content stored in source control, I would be hesitant to change this to be the default.
Perhaps there could be another way to get around the row limitation/"out of resources" issue, though: why not batch load or bulk insert the data into a temporary table first and then merge it from that? I can imagine that this would be a lot more efficient than generating the merge statement with all the data contained within a monolithic VALUES
clause. I've covered this idea in more detail here: #19 (comment)
from generate-sql-merge.
@ghost see answer to #75 - it works, I used to generate a script for 40K rows
from generate-sql-merge.
An update on this: @EitanBlumin has very helpfully implemented a new parameter that allows you to split source rows into multiple MERGE statements. To use, use @max_rows_per_batch=1000
or whatever batch size you need and be sure to also include the @delete_if_not_matched=0
param.
from generate-sql-merge.
Related Issues (20)
- Ability to handle multiple tables
- Typo HOT 1
- Creation error in Azure Sql server database HOT 2
- Column names and tabs HOT 2
- Enhancement Request : Create Merge statement with JSON parse (with an option) HOT 2
- Merge statement fails when used for processing bigger tables HOT 3
- SSMS Issue, or..? HOT 3
- @cols_to_exclude longer than 128 chars HOT 4
- sql_variant => invalid statements, basetype needs to be checked and used HOT 2
- Add reference to examples in readme.md? HOT 2
- Ambiguous column name when same column is in two tables HOT 2
- Issue with composite key and order in values HOT 2
- Using '@include_rowsaffected = 0' causes error "A MERGE statement must be terminated by a semi-colon (;)" HOT 1
- Support for Managed Instance - cant find sp_MS_marksystemobject HOT 1
- SQL Server v2019 issues HOT 3
- QUOTED_IDENTIFIER error HOT 1
- case sensitive update HOT 1
- Case mismatch object_id column name HOT 1
- Error Occurs When Verifying the Existence of the 'hashedvalue' Column HOT 1
- instruction(x)' contains an invalid XML identifier as required by FOR XML HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from generate-sql-merge.