SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuilding a table


Rebuilding a table

Author
Message
dwilliscp
dwilliscp
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7970 Visits: 844
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350840 Visits: 34260
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
drew.allen
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62307 Visits: 16628
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350840 Visits: 34260
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385779 Visits: 42547
KISS, usually a good way to go.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82838 Visits: 9352
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
dwilliscp
dwilliscp
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7970 Visits: 844
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.
dwilliscp
dwilliscp
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7970 Visits: 844
ScottPletcher - Tuesday, December 19, 2017 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).

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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search