Staging table has no data during refresh times (Populated using a Stored Procedure)-CodePudding

My work has a stored procedure that empties and then repopulates a SQL staging table multiple times a day every now and then the table returns with no values when it is refreshing, this is causing me difficulty and I need access to the data in the table. What can I do to fix this?

I was thinking maybe create a view on top of the staging table or maybe the stored procedure is coded wrong it can maybe be modified

the procedure begins with the following code:

create or replace Procedure <ProcedureName>
as
Begin

    EXECUTE IMMEDIATE 'TRUNCATE table <TableName>';
     insert into <TableName> 
END

CodePudding user response：

Between the truncate table and the commit after the insert into transaction the table will effectively be empty.

One way of solving this is to use the "exchange partitioning" technique. It is pretty well explained on the web. The idea is that the data is loaded in a staging table with a single partition and when the loading is complete, the partition of the staging table is exchanged with the partition in the main table. This process is instant - at no point the table is empty.