Как поместить динамический SQL в temptable, если я не знаю, какие заголовки столбцов будут созданы?

Question

Возможный дубликат:
Как я могу назначить нормальную таблицу из динамической сводной таблицы?

У меня есть этот запрос:

DECLARE @Col NVARCHAR(MAX) = 
    (   SELECT  ', ' + QUOTENAME(CONVERT(VARCHAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - (12 - Number), 0), 103)) + ' = [' + CAST(number AS VARCHAR) + ']'
        FROM    Master..spt_values
        WHERE   Type = 'P'
        AND     number BETWEEN 0 AND 12
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')



DECLARE @SQL NVARCHAR(MAX) = 
        N'WITH Data AS
        (   SELECT  DACP_Id, DACP_Value, ResultCenters.RSL_CodDescr AS CentroResultado, Users.USR_EmpName Colaborador, Devices.DEV_NoArea+Devices.DEV_No as AreaNumero,
                    [MonthNum] = 12 - DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP)
            FROM    DevicesAccompaniments INNER JOIN
                    Devices ON Devices.DEV_Id = DevicesAccompaniments.FK_DEV_Id INNER JOIN
                    ResultCenters ON ResultCenters.RSL_Id = Devices.DEV_RsltId INNER JOIN
                    Contracts ON Contracts.CNT_Id = Devices.DEV_ContrId INNER JOIN
                    Users ON Users.USR_Id = Devices.DEV_UsrId
            WHERE   DATEDIFF(MONTH, DACP_Date, CURRENT_TIMESTAMP) BETWEEN 0 AND 12 
        )   
        SELECT  CentroResultado, Colaborador, AreaNumero' + @Col + '
        FROM    Data
                PIVOT
                (   SUM(DACP_Value)
                    FOR MonthNum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
                ) pvt;'

Он создает 12 столбцов и дополнительную информацию о 12 прошедших месяцах с нашей текущей даты, проблема в том, что мне нужно поместить этот запрос в отчет, но мне нужно отобразить столбцы в temptable с информацией внутри var @SQL без выполнение EXEC sp_executesql @SQL; Вот образец скрипки за последние 12 месяцев с текущей даты: http://www.sqlfiddle.com/

Любая помощь приветствуется, спасибо.

Answer 1

У меня есть сохраненный скрипт, который генерирует оператор вставки на основе текущей схемы (что делает его адаптивным к будущим версиям нашей базы данных). Основная идея этого заключается в том, что он объединяет данные из одной таблицы (с совершенно другой схемой, но я знаю, что у меня есть связанные данные) и использует существующую запись в таблице назначения в качестве шаблона для заполнения отсутствующих данных.

Я действительно считаю, что этот скрипт немного сложнее, чем вам нужно. Тем не менее, я думаю, что все инструменты, которые вам понадобятся, помогут вам настроить его под свои нужды.

DECLARE @SourceID VARCHAR(50), @DestinationID VARCHAR(50), @TemplateID VARCHAR(50)
-- Replace with Source ID which has menus to convert
SELECT @SourceID = '<SCREENID>' 
-- Replace with Destination container ID which will contain the parent record for the new data.
SELECT @DestinationID = '<DestinationID >' 
-- ID of template record will will be used to fill in gaps in data.
SELECT @TemplateID = '<Template Record's ID>'

-- Initialize Cursor to gather schema about destination table.
---- This should make this script schema independent
DECLARE @ColumnBuilder NVARCHAR(4000),@ColumnTemp VARCHAR(50)
select @ColumnBuilder = ''

DECLARE ColumnCursor CURSOR LOCAL
FOR select Column_Name from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<DESTINATION TABLE NAME>' ORDER BY ORDINAL_POSITION

OPEN ColumnCursor

FETCH NEXT FROM ColumnCursor
INTO @ColumnTemp

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @ColumnBuilder = @ColumnBuilder + 'C.' + @ColumnTemp + ','

FETCH NEXT FROM ColumnCursor
INTO @ColumnTemp

END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
-- End Cursor

-- Modify some values, for example, we don't want to copy in the template's PK, instead we'll generate a newid()
SELECT @ColumnBuilder = SUBSTRING(@ColumnBuilder, 1,LEN(@ColumnBuilder)-1) -- get rid of last comma.

SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.TableID,','newid(),')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.ParentID,','''' + @DestinationID + ''',')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.ColumnINeverWantCopied,','NULL,')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.TranslateMe,','MI.TranslateableData,')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.Order,','MI.Order + C.Order,')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.EntryBy,','''TranslateScript'',')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.UpdateBy,','''TranslateScript'',')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.EntryDtm,','getdate(),')
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.UpdateDtm,','getdate(),')

-- Set Label of new buttons
SELECT @ColumnBuilder = REPLACE(@ColumnBuilder,'C.Label,','
CASE WHEN MI.Alias IS NULL
THEN
CASE WHEN MI.CommandID = 7 THEN ''Dial''
WHEN MI.CommandID = 8 THEN ''Email''
ELSE S.Alias
END
ELSE MI.AlternateAlias
END,')

-- Build INSERT statement
SELECT @ColumnBuilder = 'INSERT INTO <Destination table> SELECT ' + @ColumnBuilder + ' from 
(<Source Table> MI LEFT JOIN <Source's Parent Table> S ON MI.ParentID = S.ParentID), <Destination table> C
WHERE MI.ParentID= ''' + @SourceID  + ''' AND C.TableID = ''' + @TemplateID  + ''' 
AND MI.CommandID NOT IN (25,43,32,11,45)'

-- Return whole generated statement, for debugging
SELECT @ColumnBuilder

-- Execute!
exec sp_executesql @ColumnBuilder