Performing Insert OR Update (upsert) on sql server compact edition


Question

I have c# project that is using sqlserver compact edition and entity framework for data access. I have the need to insert or update a large amount of rows, 5000+ or more to the db, so if the key exists update the record if not insert it. I can not find a way to do this with compact edition and EF with out horrible performance, ie taking 2 mins plus on a core i7 computer. I have tried searching for the record to see if it exists then inserting if not or update if it does, the search is the killer on that. I have tried compiling the search query and that only gave a small improvement. Another thing ive tried is inserting the record in a try catch and if it fails update, but that forces me to savechanges on every record to get the exception as opposed to at the end which is a performance killer. Obviously I can't use stored procedures since it is compact edition. Also I've looked at just executing t-sql directly somehow on the db, but lack of process statements in compact seems to rule that out. I've searched the world over and out of ideas. I really wanted to use compact if I can over express for the deployment benefits and ability to prevent the user from digging around the db. Any suggesitons would be appreciated.

Thanks

1
2
4/8/2009 3:32:05 AM

Accepted Answer

Maybe you could obtain the result you seek by using simple queries. Let's say the the table you want to insert into or update is like this

TABLE original
     id integer,
     value char(100)

first you could create a temporary table with the new values (you can use a SELECT INTO or other ways to create it)

TABLE temp
    id integer,
    value char(100)

now, you need to do two things, update the rows in original and then insert the new values

UPDATE original 
SET original.value = temp.value
FROM original, temp
WHERE original.id = temp.id

INSERT INTO original 
SELECT * from temp 
WHERE temp.id not IN (select o.id from original o)
2
4/8/2009 9:31:37 PM

When we're using SQL CE (and SQL 2005 Express for that matter) we always call an update first and then call an insert if the udate gives a row count of 0. This is very simple to implement and does not require expensice try..catch blocks for control flow.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon