Where Do I Want To Go Today? - Upsert
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/wheredoiwanttogotodayupsert.asp
-
Definetly agree. I have seen in Siebel CRM system as well and it makes a lot of sense to be database feature.
-
Not bad. So you see the syntax as an an insert/values or insert/select type operation rather than an extra keyword on a trigger or stored proc? Create trigger update on blah for upsert?
Andy
-
From the dbforums site I saw a post that this was a common request and is under consideration. However, can you post a code example so people know what it is you are doing to set it off and how it decides INSERT or UPDATE. Is it based on constraint failure or whole row?
-
It would be very useful.
I personally would put an extension to the insert command like "WITH UPDATEEXISTING"
Same for the BCP or DTS and bulk insert.
I think the biggest help could go to the BCP like data load.
Bye
Gabor -
How about a SELEDELETUPSERTIFCASEDBCCBAKRESTORE statement that does everything at once? You can just run a SELEDELETUPSERTIFCASEDBCCBAKRESTORE and be out of the office by 9:30.
John Scarborough
MCDBA, MCSA -
john2, that's a good one. No it's I'm not looking for a end all be all, but it is a nice feature, especially for bulk insert operations when you repeat then.
How many times have you tried to load data that had a duplicate PK and it fails. Be nice to have the upsert work on a set of data based on the PKs. Like with an update, you would supply the PK and other values.
So in the Northwind db.
upsert employees
values (employeeid, lastname)
select 8, 'Jones'
This would check the employees table for an employeeid (PK) of 8. If one exists, the last name is updated to 'Jones' and everything else is left alone. If it does not exist, a new row with employeeid = 8 and lastname = 'Jones' is inserted. This would be subject to the same insert rules and would require defaults or nulls allowed for all other fields.
Steve Jones
-
I actually like the MySQL INSERT INTO ... ON DUPLICATE KEY UPDATE syntax. Very clear and easy to understand.
-
Yeah buddy! Like you said, it's not that big a deal to code around the problem, but this situation arises all the time. It should be a core DB feature.
-
When you are working with data in a datawarehouse, insert / actualize data is a common task. If we could minimize this task: Excellent!!!
Leonel E. Umaña Araya
Leonel E. Umaña Araya
leo_umana@hotmail.com -
quote:
I actually like the MySQL INSERT INTO ... ON DUPLICATE KEY UPDATE syntax. Very clear and easy to understand.that's a really a useful feature (AFAIK, only in Version 4 and above). I'm running 3.23.57
In addition, I would like to mention the LIMIT clause.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
In addition, I would like to mention the LIMIT clause.Actually, I find SQL Server's TOP clause more versatile.
-
I like the idea behind WITH UPDATEEXISTING.
I feel it needs three settings.
- ON - Does what it says on the tin.
- OFF - Performs INSERTS only but throws errors if primary keys are violated.
- IGNORE - Performs INSERTS only but does not process duplicated primary key and therefore does not need to throw key violation errors.
The next version of .NET is supposed to reduce code by 70% anything that reduces the need for code should be applauded.
-
I like this idea, Oracle has a similar command but their version is unfortunately structured in a way that makes it difficult (if not impossible) to code against.
But very useful in terms of loading data to a data warehouse. Not sure how useful it would be with bcp, since bcp is more of a bulk load tool than a DML or data scrubbing tool...
---
Resident DBMS fence sitter.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply