将临时表更改为声明它们

Question

只是一个关于临时表和声明表的问题。如果我将临时表“ChangedData”和“PackageDatatoProcess”更改为它们自己的变量“@ChangedData”和“@PackageDatatoProcess”，我是否可以询问如何将select语句更改为语句，因为我以前还没有这样做过。实际上，我告诉过我们可以声明表，而不是使用select into，但是在这方面需要一些帮助：

select distinct * into #PackageDataToProcess from #ChangedData pp
        outer apply (
            select pk.Reference, pjl.PackageToJournalLinkId, j.CreatedDate, pccl.PackageCostChangeLogId from Jet2Holidays.dbo.Package pk
            inner join Jet2Holidays.dbo.PackageToJournalLink pjl on pk.PackageId = pjl.PackageId
            inner join Jet2Holidays.dbo.Journal j on pjl.JournalId = j.JournalId
                and j.PrincipalName= iif(@AllowNonSupportChanges = 0, 'HolidaysSupport', j.PrincipalName) 
            inner join Jet2Holidays.dbo.BusinessProcess bp on pjl.BusinessProcessId = bp.BusinessProcessId
                and bp.[Description] =  iif(@AllowNonSupportChanges = 0, 'CallCentreAction', bp.[Description]) 
            left outer join Jet2Holidays.dbo.PackageCostChangeLog pccl on pccl.PackageToJournalLinkId = pjl.PackageToJournalLinkId
            where pk.Reference = pp.PackageReference
            and pp.JournalID = pjl.JournalId

            ) as packageData

Answer 1

首先，声明变量表如下所示：

DECLARE @PackageDataToProcess TABLE
(
    Reference UNIQUEIDENTIFIER
,   PackageToJournalLinkId INT
,   CreatedDate DATETIME
,   PackageCostChangeLogId INT
,   {other columns here}
)

DECLARE @ChangedData TABLE
(
    Reference UNIQUEIDENTIFIER
,   PackageToJournalLinkId INT
,   CreatedDate DATETIME
,   PackageCostChangeLogId INT
,   {other columns here}
)

此时，您可以填充@ChangedData表，如下所示：

INSERT @ChangedData ( Reference, PackageToJournalLinkId, CreatedDate, PackageCostChangeLogId, {other columns})
SELECT Reference, PackageToJournalLinkId, CreatedDate, PackageCostChangeLogId, {other columns} 
   FROM ChangedDataSource -- Table, Procedure, Function

然后可以通过将@PackageDataToProcess替换为#PackageDataToProcess来运行代码。这里再次进行了一次轻微的重写(从原来的)：

    INSERT @PackageDataToProcess
    select distinct pp.* from @ChangedData pp
    outer apply (
        select pk.Reference, pjl.PackageToJournalLinkId, j.CreatedDate, pccl.PackageCostChangeLogId from Jet2Holidays.dbo.Package pk
        inner join Jet2Holidays.dbo.PackageToJournalLink pjl on pk.PackageId = pjl.PackageId
        inner join Jet2Holidays.dbo.Journal j on pjl.JournalId = j.JournalId
            and j.PrincipalName= iif(@AllowNonSupportChanges = 0, 'HolidaysSupport', j.PrincipalName) 
        inner join Jet2Holidays.dbo.BusinessProcess bp on pjl.BusinessProcessId = bp.BusinessProcessId
            and bp.[Description] =  iif(@AllowNonSupportChanges = 0, 'CallCentreAction', bp.[Description]) 
        left outer join Jet2Holidays.dbo.PackageCostChangeLog pccl on pccl.PackageToJournalLinkId = pjl.PackageToJournalLinkId
        where pk.Reference = pp.PackageReference
        and pp.JournalID = pjl.JournalId

        ) as packageData

您声明的表将以类似于非全局临时表的方式超出范围。