将表格变量插入到具有附加值的其他表格中

Question

因此，我正在使用游标循环查看返回的一堆记录。我刚更新了表中的一些细节，现在我想从该表中提取细节，因此我使用了临时表。

所以现在我想一些值插入到一个新表是无关的最后一个，然后将其余值会从表变量直接拷贝...我怎样才能做到这一点？

我会后有问题的部分下方，以帮助人们看到什么，我试图做的。 有问题的部分位于更新状态评论和上述未完成评论之间。

OPEN cur 
FETCH NEXT FROM cur INTO @MembershipTermID , @EndDate , @MembershipID <VARIABLES> 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    --PERFORM ACTION 
    DECLARE @TodaysDate DATETIME 
    SET @TodaysDate = getDate() 

    --CANCEL DETAIL 
    DECLARE @CancellationDetailID INT 
    INSERT INTO CancellationDetail(CancellationDetailID,RefundAmount,OldEndDate,EffectiveDate,CancelDate,ReasonCodeProgKey) 
    VALUES (0, 0.0, @EndDate, @TodaysDate, @TodaysDate, 'CANC_DORMANT') 
    SELECT @CancellationDetailID = SCOPE_IDENTITY()  
    INSERT INTO CancellationDetailAudit(StampUser,StampDateTime,StampAction,CancellationDetailID,RefundAmount,OldEndDate,EffectiveDate,CancelDate,ReasonCodeProgKey) 
    VALUES('SYSTEM', GetDate(), 'I', @CancellationDetailID, 0.0, @EndDate, @TodaysDate, @TodaysDate, 'CANC_DORMANT') 

    --LINK TO TERM 
    INSERT INTO MembershipTermCancellationDetail(CancellationDetailID,MembershipTermID) 
    VALUES(@CancellationDetailID, @MembershipTermID) 
    INSERT INTO MembershipTermCancellationDetailAudit(StampUser,StampDateTime,StampAction,MembershipTermCancellationDetailID,CancellationDetailID,MembershipTermID) 
    VALUES('SYSTEM', GetDate(), 'I', 0, @CancellationDetailID, @MembershipTermID) 

    --UPDATE STATUS 
    UPDATE MembershipTerm 
    SET MemberStatusProgKey = 'CANCELLED', 
    EndDate = @TodaysDate, 
    UpdateDateTime = @TodaysDate, 
    AgentID = 224, 
    NextTermPrePaid = 'False' 
    WHERE MembershipTermID = @MembershipTermID 

    DECLARE @MembershipTermTable TABLE 
    (
    MembershipTermID int, 
    MemberStatusProgKey nvarchar (50), 
    StartDate datetime, 
    EndDate datetime, 
    AdditionalDiscount float, 
    EntryDateTime datetime, 
    UpdateDateTime datetime, 
    MembershipID int, 
    AgentID smallint, 
    PlanVersionID int, 
    ForceThroughReference nvarchar (255), 
    IsForceThrough bit, 
    NextTermPrePaid bit, 
    IsBillingMonthly bit, 
    LastPaymentDate datetime, 
    PaidToDate datetime, 
    IsIndeterminate bit 
    ) 

    INSERT INTO @MembershipTermTable 
    SELECT MembershipTermID, 
    MemberStatusProgKey, 
    StartDate, 
    EndDate, 
    AdditionalDiscount, 
    EntryDateTime, 
    UpdateDateTime, 
    MembershipID, 
    AgentID, 
    PlanVersionID, 
    ForceThroughReference, 
    IsForceThrough, 
    NextTermPrePaid, 
    IsBillingMonthly, 
    LastPaymentDate, 
    PaidToDate, 
    IsIndeterminate 
    FROM MembershipTerm 
    WHERE MembershipTermID = @MembershipTermID 

    INSERT INTO MembershipTermAudit(StampUser,StampDateTime,StampAction,MembershipTermID,MemberStatusProgKey,StartDate,EndDate,AdditionalDiscount,EntryDateTime,UpdateDateTime,MembershipID,AgentID,PlanVersionID,ForceThroughReference,IsForceThrough,NextTermPrePaid,IsBillingMonthly,LastPaymentDate,PaidToDate,IsIndeterminate) 
    VALUES ('SYSTEM',@TodaysDate,'I',MembershipTermID,MemberStatusProgKey,StartDate,EndDate,AdditionalDiscount,EntryDateTime,UpdateDateTime,MembershipID,AgentID,PlanVersionID,ForceThroughReference,IsForceThrough,NextTermPrePaid,IsBillingMonthly,LastPaymentDate,PaidToDate,IsIndeterminate) 
    --ABOVE NOT FINISHED, NEED TO ADD AUDIT RECORD CORRECTLY 

    --Members 
    DECLARE @MembersTable TABLE 
    (
    MembershipTermID int, 
    MemberStatusProgKey nvarchar (50), 
    StartDate datetime, 
    EndDate datetime, 
    AdditionalDiscount float, 
    EntryDateTime datetime, 
    UpdateDateTime datetime, 
    MembershipID int, 
    AgentID smallint, 
    PlanVersionID int, 
    ForceThroughReference nvarchar (255), 
    IsForceThrough bit, 
    NextTermPrePaid bit, 
    IsBillingMonthly bit, 
    LastPaymentDate datetime, 
    PaidToDate datetime, 
    IsIndeterminate bit 
    ) 

    INSERT INTO @MembersTable 
    SELECT * FROM [MembershipTermPerson] WHERE MembershipTermID = @MembershipTermID 


    --Vehicles 





    FETCH NEXT FROM cur INTO @MembershipTermID , @EndDate , @MembershipID <VARIABLES> 
END 

CLOSE cur 
DEALLOCATE cur 

Answer 1

我觉得

喜欢的东西

INSERT INTO MyTable (ColA, ColB, ColC) 
SELECT 
    GETDATE(), A.MyCol, 'MyValue' 
FROM MyOtherTable A 
WHERE a.MyValue = 'What I Want' 

基本上你跳过临时表，只是抢值和注入的一切，这将是一个INSERT INTO SELECT语句中一个很好的例子一旦。