sql server delete when all data are selected

Question

I add a stored procedure which delete and insert data of a specific table but the executing time of the select can make 10 minutes to finished so during that time my table is empty

How can I modify my stored procedure to select all the needed data when it's finish it delete on the target table the current data and insert the data selected ?

This is my code

delete from table_b

insert into table_b(id,name,km)
    select id,t.name,t.kmfrom table_a
    OUTER APPLY (select * from dbo.calculate(table_a.CoordonneeX,table_a.CoordonneeY)) as t

Answer 1

An analogy:

• You have a table full of vegetables you're selling at the local farmer's market.
• When you bring a new load of vegetables, it's going to take you 20 minutes to clear off the table and replace the stock with the newer product.
• You don't want customers to sit there and wait 20 minutes for the switch to happen (most will just buy vegetables from someone else).

What if you had a second empty table, where you load the new vegetables, and while you're doing that, customers can still buy the older vegetables from the first table? (Let's pretend it's not that the older vegetables went bad or are otherwise less desirable.)

There are multiple ways to do that to satisfy your scenario. The basic concept is:

• You have a second shadow table that you load in the background.
• While this background load happens, users continue seeing the older data in the first table.
• When the background load is finished, you can redirect users to the second table with newer data via:
  • rename
  • changing synonyms or views to point at the new table
  • transferring between schemas (see here and here)
  • partition switching (in spite of popular belief, Enterprise Edition not required)

I've always been a fan of using schemas for this, but partition switching is the best answer because the other three solutions require more aggressive schema modification locks, don't allow for waiting at lower priority, and also have a greater risk of invalidating or making less accurate any existing statistics / execution plans. A problem with all of these solutions is if there are foreign keys pointing to the main table, which you'll have to deal with no matter what when part of your plan is to empty the parent table.

Borrowing heavily from Kendra's gist, because I'm lazy and she did a fantastic job there, let's create two copies of our original table, one for handling the incoming new data, and one for accepting the old data:

CREATE TABLE dbo.MyTable
(
  id int NOT NULL,
  description varchar(32)
);

INSERT dbo.MyTable(id, description) VALUES(1, 'old data');

CREATE TABLE dbo.MyTable_Staging
(
  id int NOT NULL,
  description varchar(32)
);

CREATE TABLE dbo.MyTable_Garbage
(
  id int NOT NULL,
  description varchar(32)
);

SELECT * FROM dbo.MyTable;

(This is a really simple model - of course your real tables would have matching primary keys, indexes, constraints, and so on.)

Now, we can load the staging table in the background, and when that's done, switch out the current data into the garbage table, then switch the staging table into the main table.

-- perform the background loading outside of any blocking transaction:

TRUNCATE TABLE dbo.MyTable_Garbage;
TRUNCATE TABLE dbo.MyTable_Staging;
INSERT dbo.MyTable_Staging(id, description) 
  VALUES(1, 'new data'),(2, 'new row!');

BEGIN TRANSACTION;

  ALTER TABLE dbo.MyTable
    SWITCH TO dbo.MyTable_Garbage
    WITH ( WAIT_AT_LOW_PRIORITY 
      ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
  );  

  ALTER TABLE dbo.MyTable_Staging
    SWITCH TO dbo.MyTable;  
    
COMMIT TRANSACTION;
    
SELECT * FROM dbo.MyTable;

I demonstrate this in a db<>fiddle, except that the permissions there don't allow us to specify the wait at lower priority option for blockers, which are going to be important at scale. There is also no error handling for simplicity, but that doesn't mean it's not required. (And thanks @Charlieface for pointing out that PARTITION 1 is unnecessary for a non-partitioned table.)

You can clean out the garbage sooner, too, like right after the commit, but keeping it around lets you troubleshoot or revert to it if there is some kind of issue.