upsert

Elegant way to handle upsert with Hibernate and MySQL

I'm currently working on a batch import feature that sits on top of Hibernate and MySQL. My goal is to have Upsert functionality for several tables. I'm finding myself writing a lot of code to deal with seeing if the row exists by key and branching to right method. I was wondering if there might be a better way, i.e. something analogous ...

High performance SSIS Custom Components for Upsert

Anybody know vendor for fast upsert components in SSIS So far I found one from Pragmaticworks - TaskFactory - SSIS Tasks and components I was very impressed with Upsert, Data Clenasing, SFTP and Zip (Much faster than any other solution out there) ...

Oracle MERGE does not INSERT

I have this simple example I can't seems to get working : MERGE INTO mytable mt USING dual ON (mt.id = 'AAA' ) WHEN MATCHED THEN UPDATE SET mt.name = 'updated' WHEN NOT MATCHED THEN INSERT (mt.id , mt.name ) VALUES ('AAA', 'Gooood' ); If a 'AAA' record exists in the table, it is updated successfully. But if does no...

syntax for single row MERGE / upsert in SQL Server

I'm trying to do a single row insert/update on a table but all the examples out there are for sets. Can anyone fix my syntax please: MERGE member_topic ON mt_member = 0 AND mt_topic = 110 WHEN MATCHED THEN UPDATE SET mt_notes = 'test' WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test') Resolution per ...

Atomic UPSERT in SQL Server 2005

What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists, INSERT otherwise) in SQL Server 2005? I see a lot of code on SO (e.g. see http://stackoverflow.com/questions/639854/tsql-check-if-a-row-exists-otherwise-insert) with the following two-part pattern: UPDATE ... FROM ... WHERE <condition> -- race condition risk...

SQLite UPSERT - ON DUPLICATE KEY UPDATE

MySQL has something like this: INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1; As far as I'm know this feature doesn't exist in SQLite, what I want to know is if there is any way to archive the same effect without having to execute two queries. Also, if this is not possible, what do you p...

Oracle - UPSERT with update not executed for unmodified values

I'm using following update or insert Oracle statement at the moment: BEGIN UPDATE DSMS SET SURNAME = :SURNAME WHERE DSM = :DSM; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO DSMS (DSM, SURNAME) VALUES (:DSM, :SURNAME); END IF; END; This runs fine except that the update statement performs dummy update if the ...

How do I do batch upserts in SQL Server?

I'm using the MERGE statement to upsert rows in an sql server 2008 database. However, my sproc is a single-row operation, whereas in fact I'd prefer to batch these. Is this even possible and, if so, how do I do it? ...

Dynamic upsert in postgresql

I have this upsert function that allows me to modify the fill_rate column of a row. CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS ' DECLARE num ALIAS FOR $1; dat ALIAS FOR $2; BEGIN LOOP -- First try to update. UPDATE alarming SET fill_rate = dat WHERE equipid = num; IF FOUND TH...

Question about inserting/updating rows with SQL Server (ASP.NET MVC)

I have a very big table with a lot of rows, every row has stats for every user for certain days. And obviously I don't have any stats for future. So to update the stats I use UPDATE Stats SET Visits=@val WHERE ... a lot of conditions ... AND Date=@Today But what if the row doesn't exist? I'd have to use INSERT INTO Stats (...) VALUES...

Replace into equivalent for postgresql and then autoincrementing an int

Okay no seriously, if a postgresql guru can help out I'm just getting started. Basically what I want is a simple table like such: CREATE TABLE schema.searches ( search_id serial NOT NULL, search_query character varying(255), search_count integer DEFAULT 1, CONSTRAINT pkey_search_id PRIMARY KEY (search_id) ) WITH ( OIDS=FALSE ...

check if record exists "update" if not "insert" stored procedure

hey guys I got this. I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table.. what am I doing wrong? It increases timeValid for +30 days but inserts another row too.. thank you.. Select @pre_var = count(*) From PREMIUM_...

What commit styles are available in MongoDB?

In some sense, the default "save" operation appears to be asynchronous in MongoDB. It seems that when a client saves a document, it will typically receive a successful response immediately from the server, even if the operation has not yet been applied to the database (let alone committed to disk). (I'm basing this model for how save()...

Postgres UPSERT (INSERT or UPDATE) only if value is different

I'm updating a Postgres 8.4 database (from C# code) and the basic task is simple enough: either UPDATE an existing row or INSERT a new one if one doesn't exist yet. Normally I would do this: UPDATE my_table SET value1 = :newvalue1, ..., updated_time = now(), updated_username = 'evgeny' WHERE criteria1 = :criteria1 AND criteria2 = :crite...

Is there a way to upsert a list with a single query?

Hi, I know this question has been asked before, but that's a different scenario. I'd like to have a collection like this: { "_id" : ObjectId("4c28f62cbf8544c60506f11d"), "pk": 1, "forums": [{ "pk": 1, "thread_count": 10, "post_count": 20, }, { "pk": 2, "thread_count": 5, ...

How to mimic upsert behavior using Hibernate?

Hello all, I'm writing an application that sync's entities from a third party datasource into our own schema, with a transformation/mapping step in between. I'm using Hibernate to represent and persist the entities in our own schema. A problem I'm running into is that I have a unique multi-column key on one of my tables. The behavior...

MySql upsert and auto-increment causes gaps

I've got a MySql table with an auto-increment primary key, and it seems that all of the various upsert methods (INSERT IGNORE and ON DUPLICATE KEY UPDATE) suffer from the, uh, feature that the auto-increment field increments, even if a row is updated and not inserted. This means that gaps are introduced into the table, which I find unde...

Is LockModeType.PESSIMISTIC_WRITE sufficient for an UPSERT in JPA?

I've read this article on JPA concurrency, but either I am too thick or it is not explicit enough. I am looking to do a database-controlled atomic update-if-found-else-insert operation (an UPSERT). It looks to my poor slow brain that I can--within a transaction of course--run a named query with a lock mode of PESSIMISTIC_WRITE, see if ...

Why doesn't this specific syntax work for upserting?

Hello, I'm using SQL Server 2005 and I want to synchronize two tables which have the same definition but exist in different databases. MERGE INTO only exists in 2008 and I'd prefer a syntax where I don't have to specify columns in the UPDATE. So I stumbled upon various posts using the following syntax: UPDATE Destination FROM (Source I...

Can Upsert be more effective if we have more data ?

Hi, I have to delete some amnt of data and insert some into same table. Will there be any performance improvement if we go for Upsert in a loop ? Thanks in advance! ...