CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Luke101
Luke101

Reputation: 65278

How to delete all data then insert new data

I have a process that runs every 60 minutes. On one table I need to remove all data then insert records from a different table. The problem is it takes a long time to delete and reinsert the data. When the table has no data I am afraid the users will see this. Is there a way to refresh the data without users seeing this?

Upvotes: 0

Views: 7015

Answers (5)

Gilad Green
Gilad Green

Reputation: 37299

If you want to remove all data from the table then use the TRUNCATE TABLE instead of delete - It'll do it faster.

As for the insert it is a bit hard to say because you did not give any details but what you can try is:

  • Option 1 - Using temp table
create table table_temp as select * from original_table where rownum < 1;
//insert into table_temp

drop table original_table;
Exec sp_rename 'table_temp' , 'original_table'
  • Option 2 - Use 2 tables "Active-Passive" - Have 2 tables for the data and a view to select over them. The view will join with a third table that will specify from which of the tables to select. kind of an "active-passive" concept. To demonstrate concept:
with active_table as ( select 'table1_active' active_table )

select 1 data     
where 'table1_active' in (select * from active_table)

union all     
select 2  
where 'table2_active' in (select * from active_table)

//This returns only one record with the "1"

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28920

I would follow below approach. While I troubleshoot why the delete and reinsert is taking time.

  1. Create a new table ( t1 ) which has same data as oldtable ( maintable )
  2. Now do your stuff on t1.
  3. When your stuff is done, rename t1 to maintable.

Upvotes: 0

Cato
Cato

Reputation: 3701

Make it sequence based, your copied in records all have have a series number (all the same for all copied in records) and another file holds which sequence is active, and you always select on a join to this table - when you copy in new records they have a new sequence that is not yet active, when they are all copied in, then the sequence table is updated to the new sequence - the redundant sequence records are deleted at your leisure.

Example

Let's suppose your table has field SeriesNo added and table ActiveSeries has field SeriesNo.

All queries of your table:

SELECT * 
FROM YourTable Y 
JOIN ActiveSeries A 
       ON A.SeriesNo = Y.SeriesNo

then updating SeriesNo in ActiveSeries makes new series of records available instantly.

Upvotes: 0

bitch_cakes
bitch_cakes

Reputation: 126

If you are deleting all rows in a table, you can consider using a TRUNCATE statement against the table instead of a DELETE. It will speed up part of your process. Keep in mind that this will reset any identity seeds you may have on the table. As suggested, you can wrap this process in a transaction and depending on how you set your transaction isolation level, you can control what your users will see if they query the data during the transaction.

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

Are you truncating instead of deleting? A truncate (while logged) is much, much, faster then a delete.

If you cannot truncate try deleting 1000-10000 rows at a time (smaller log buildup and on deleting large amounts of rows great increase in speed.)

If you really want fast performance you can create a second table, fill it with data, and then drop the first table and rename the second table as the first table. You will lose all the permissions on the table when you do this so be sure to reapply the permissions to the renamed table.

Upvotes: 0

Related Questions