December 19, 2017 at 9:00 am
I have a table where I need to run a delete and then an insert into...
The data from the source has no PK
How do I do this.. and keep the table from being accessed? From time to time.. we get and empty table when reading ... during this refresh?
December 19, 2017 at 9:40 am
To keep the table from being accessed, well, it really depends a lot on your system, the configuration etc. If you do not have SA or DBO for all your logins (a depressingly common situation), simply revoke access to that table while doing your load (although, you do know that's going to cause the potential for headaches). An alternative would be to load everything to another table and then merge into your active table. Or, load to another table with a different name, then swap out the names. Or, change the load process so that it doesn't simply delete everything and then reload everything (seems a little sketchy). Of course people querying are going to see data going away, it's what you're doing after all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2017 at 10:27 am
I was thinking that for most purposes, an explicit transaction would suffice.
BEGIN TRAN
DELETE statement
INSERT statement
COMMIT TRAN
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 19, 2017 at 12:44 pm
Yeah that could do it fine too. It will likely block the table for a time (depending).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2017 at 12:57 pm
KISS, usually a good way to go.
December 19, 2017 at 2:29 pm
If you can, TRUNCATE the table rather than DELETE all the rows, as trunc will be much less overhead (btw, in case you're wondering, a trunc can be rolled back in SQL Server, unlike in, say, Oracle).
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".
December 25, 2017 at 8:57 am
I was uncertain about the locking going on during a Begin Trans --- commit, but I will try that. I can not revoke rights since our vendor has everyone as database owner. (yea I hate that) After you log into their app..it checks using the windows user ID.
December 25, 2017 at 8:59 am
ScottPletcher - Tuesday, December 19, 2017 2:29 PMIf you can, TRUNCATE the table rather than DELETE all the rows, as trunc will be much less overhead (btw, in case you're wondering, a trunc can be rolled back in SQL Server, unlike in, say, Oracle).
I am trying to put all my re-fresh code in one place.. so would not be able to do cross server truncate... and would have to put the update code in each database... granted not a big deal, but I like to have all the import of SAP data... and pushing out .. in one place.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply