SQL Server: Staging tables vs Local Temp tables

Many of us use staging tables to refresh master table data from other sources. The usual logic is truncating everything from stage table and insert/update/delete or merge the master table by using it. This is fine for tables with less data. For the tables, which has GBs of data will occupy lot of physical storage all the time, which is not good practice. Alternate to the method we can create local or global temporary tables in the Stored Procedures and use them to refresh the master table data.

These local/global temporary tables are created when the procedure execution started and dropped at the end of the execution. This will consume the memory though but it release once the procedure execution is completed.

Below is the complete script of a stored procedure including creating and dropping temporary table, insert and update master table using it.

CREATE PROCEDURE [dbo].[usp_LocalTempTableDemo]

AS

BEGIN

CREATE TABLE #LocalTempTable

(

    [LocatorName] [nvarchar](40) NULL,

       [InventoryLocationID] [int] NOT NULL,

       [OrganizationID] [int] NOT NULL,

       [SubInventoryCode] [nvarchar](10) NULL,

       [Reservable] [nvarchar](1) NULL,

       [OrganizationCode] nvarchar(3) NULL

)

INSERT INTO #LocalTempTable

                       ([LocatorName]

                       ,[InventoryLocationID]

                       ,[OrganizationID]

                       ,[SubInventoryCode]

                       ,[Reservable]

                       ,[OrganizationCode])

                 select * from openquery(TEST,'select

                     LOCATOR_NAME,

                        INVENTORY_LOCATION_ID,

                        ORGANIZATION_ID,

                        SUBINVENTORY_CODE,

                        RESERVABLE,

                        ORGANIZATION_CODE  from XXX.Local_Temp_Table')

 IF (SELECT COUNT(*) FROM #LocalTempTable)   > 0

BEGIN

Update [dbo].[MasterTable]

set                        [LocatorName]=slmt.[LocatorName]

                        ,[SubInventoryCode]=slmt.[SubInventoryCode]

                        ,[Reservable]=slmt.[Reservable]

                        ,[OrganizationCode]=slmt.[OrganizationCode]

    FROM #LocalTempTable slmt

    WHERE slmt.[InventoryLocationID]=[dbo].[StocklocatorMaster].[InventoryLocationID]

       AND slmt.[OrganizationID]=.[dbo].[StocklocatorMaster].[OrganizationID]

INSERT INTO [dbo].[MasterTable]

              ([LocatorName]

                       ,[InventoryLocationID]

                       ,[OrganizationID]

                       ,[SubInventoryCode]

                       ,[Reservable]

                       ,[OrganizationCode])

                              SELECT distinct slmt.[LocatorName]

                       ,slmt.[InventoryLocationID]

                       ,slmt.[OrganizationID]

                       ,slmt.[SubInventoryCode]

                       ,slmt.[Reservable]

                       ,slmt.[OrganizationCode] FROM #LocalTempTable slmt

                 left outer join [dbo].[MasterTable] slm

                 on slmt.[InventoryLocationID]=slm.[InventoryLocationID]

              AND slmt.[OrganizationID]=slm.[OrganizationID]

                 where slm. [InventoryLocationID] is null

                 AND slm.[OrganizationID] is null

END

delete from [dbo].[MasterTable] 

from [dbo].[MasterTable] d

left join  #LocalTempTable s on s.[InventoryLocationID]=d.[InventoryLocationID] and s.[OrganizationID]=d.[OrganizationID]

where s.[OrganizationID] is null and s.[InventoryLocationID] is null

DROP TABLE #LocalTempTable

END