问最小化表锁的持续时间

EN

ALTER procedure [dbo].[Table_Generate] as

declare @d datetime = getdate(), @c as int

--Check temp tables
IF OBJECT_ID('tempdb..#final') IS NOT NULL
    DROP TABLE #final

IF OBJECT_ID('tempdb..#base') IS NOT NULL
    DROP TABLE #base

--Get source data from linked server
select
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode
into #base


from    [LinkedServer].[Database].dbo.[A_View]

--Generate row_hash
select 
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode,
    hashbytes('SHA2_256',(
     select
        ID, 
        Reference, 
        StartDate, 
        EndDate, 
        Description,
        SomeCode 
     from #base sub where sub.ID = main.ID for xml raw)) as row_hash
    into #final
    from #base main

select @c = count(*) from #final
if @c >0 begin

merge [The_Table_Staging] as target
using #final as source
on source.ID = target.ID

--New rows
when not matched by target then
insert (    RunDate,
            ID, 
            Reference, 
            StartDate, 
            EndDate, 
            Description,
            SomeCode,
            Row_Hash
        ) values (
            @d,
            source.ID, 
            source.Reference, 
            source.StartDate,
            source.EndDate, 
            source.Description,
            source.SomeCode,
            source.row_hash)

--Existing changed rows
when matched and source.row_hash != target.row_hash then update set
     target.RunDate         = @d
    ,target.Reference       = source.Reference
    ,target.StartDate       = source.StartDate
    ,target.EndDate         = source.EndDate
    ,target.Description     = source.Description
    ,target.SomeCode        = source.SomeCode
    ,target.row_hash        = source.row_hash

--Deleted rows
when not matched by source then delete;

--Existing unchanged rows
update [The_Table_Staging] set RunDate = @d where RunDate != @d

--Commit changes
begin transaction

exec sp_rename 'The_Table_Live', 'The_Table_Staging_T'
exec sp_rename 'The_Table_Staging', 'The_Table_Live'
exec sp_rename 'The_Table_Staging_T', 'The_Table_Staging'

commit transaction
end