sql - 如何在 while 循环中多次存储到临时表中？

Question

我正在做一个内部连接，每次它都会将一个新列附加到我的表中

例如，我正在尝试这样做:

WHILE @Counter <= @MaxCount
BEGIN
    SELECT @ParameterDefinitionID = ParameterDefinitionID FROM #CachedParameterDefinitionIDs WHERE RowNumber = @Counter
    SELECT * INTO #ContactsWithPatientID FROM #ContactsWithPatientID INNER JOIN (SELECT Parameter2.ContactID AS 'Parameter2ContactID', Parameter2.Value AS FirstName FROM #CachedParameterValues Parameter2 WHERE ParameterDefinitionID = @ParameterDefinitionID) FirstNameTable ON #ContactsWithPatientID.ContactID = FirstNameTable.Parameter2ContactID
    SET @Counter = @Counter + 1
END

它在第一次迭代时不起作用，因为:

There is already an object named '#ContactsWithPatientID' in the database.

我怎样才能让它发挥作用？有没有其他方法可以做到这一点？我这样做是因为我需要在查询中附加一定数量的行作为列。

Answer 1

先创建表

CREATE TABLE #ContactsWithPatientID (...)
WHILE @Counter <= @MaxCount
BEGIN
    SELECT @ParameterDefinitionID = ParameterDefinitionID 
    FROM #CachedParameterDefinitionIDs
    WHERE RowNumber = @Counter

    INSERT #ContactsWithPatientID
    SELECT *
    FROM #ContactsWithPatientID 
            INNER JOIN 
            (SELECT Parameter2.ContactID AS 'Parameter2ContactID', Parameter2.Value AS FirstName 
            FROM #CachedParameterValues Parameter2 
            WHERE ParameterDefinitionID = @ParameterDefinitionID) FirstNameTable ON #ContactsWithPatientID.ContactID = FirstNameTable.Parameter2ContactID

    SET @Counter = @Counter + 1
END

这几乎肯定也可以作为递归 CTE 一次性完成