Delete, Insert and update in the same query
-
Hi
This is probably very wishful thinking but is there a way of doing a delete, insert and update query at the same time.
so for instance i have a table in a database that feeds a live site, this table cant be empty not even for a fraction of a second.
it is updated from a view from another database. The view has all the same fields and to get the two the same i need to first delete all the rows in the table that arent in the view, then insert all the rows that are in the view that arent in the table then update all of the rows with any new information that were in both view and table.
I hope that makes sense
Thanks in advance
-
Have a look at the MERGE statement
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
i like it thanks
-
Have a look at Peter Larsson's Composable DML blog entry.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Sounds like RCS (Read-Committed Snapshot) is what you need here.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
ScottPletcher (7/17/2012)
Sounds like RCS (Read-Committed Snapshot) is what you need here.Why would that be any better than a simple MERGE?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/18/2012)
ScottPletcher (7/17/2012)
Sounds like RCS (Read-Committed Snapshot) is what you need here.Why would that be any better than a simple MERGE?
It provides continuous availability of the data, with no interruption.
A large MERGE would encumber at least parts of the table, possibly for a relatively long period of time (compared to the stated requirements).
I'm basing this on the originally stated requirements:
i have a table in a database that feeds a live site, this table cant be empty not even for a fraction of a second.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Got it. Thanks, Scott. I haven't had to work with snapshots because the boys in OPs use some form of San replication to do the same thing for us.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply