TSQL Insert N into fixed temp table

Question

I got a query with a dynamic pivot that will return N columns. I'd like to insert the result into a temp table that has a fixed number of columns defined.

So let's say the query returns 4 columns and the temp table has 20 columns, how could I insert the result into the first 4 columns and leave the rest null?

Temp table:

create table #tempTable (
Id bigint, [Name] uniqueidentifier,
Column_1 nvarchar(max), Column_2 nvarchar(max), Column_3 nvarchar(max), Column_4 nvarchar(max), Column_5 nvarchar(max),
Column_6 nvarchar(max), Column_7 nvarchar(max), Column_8 nvarchar(max), Column_9 nvarchar(max), Column_10 nvarchar(max),
Column_11 nvarchar(max), Column_12 nvarchar(max), Column_13 nvarchar(max), Column_14 nvarchar(max), Column_15 nvarchar(max),
Column_16 nvarchar(max), Column_17 nvarchar(max), Column_18 nvarchar(max), Column_19 nvarchar(max), Column_20 nvarchar(max)
)

Code I use to insert:

INSERT INTO #tempTable VALUES (@query)

Returns the error "Column name or number of supplied values does not match table definition."

I have a variable that counts the number of columns the pivot will add, maybe I could do something with this?

Answer 1

At the bottom of my answer to Dynamic columns depend on previous dynamic columns - TSQL I have an example of a dynamic insert from a pivot.

The trick is that you need to dynamically define the insert command, as well as well as dynamically making the pivot.

In the example there, I used the following

DECLARE @sql3 nvarchar(max)
SET @sql3 = N'
;WITH StockPivotCTE AS
        (SELECT  *
            FROM (SELECT StockNum, BucketName, StockAtEnd
                    FROM #StockProjections
                ) StockSummary
        PIVOT
            (SUM(StockAtEnd)
            FOR [BucketName]
            IN (' + @columnList + N')
            ) AS StockPivot
        )
    INSERT INTO #tmp (StockNum, ' + @columnList + N')
        SELECT StockNum, ' + @columnList + N'
        FROM StockPivotCTE'
EXEC (@sql3)

In the above, the column names on the table and the pivot matched (they were the 'Bucketname' values - it was bucketing sales by month dynamically). However, in your situation, you'd likely want two column lists - one for the Pivot and one for the insert.

I'm guessing you already have the columnlist for the Pivot (as you usually need one to do it dynamically) so you should only need to focus on making the correct column list for inserts.

If you need additional info, feel free to comment here - and it would help if you included the code used to create the pivot (maybe in the question rather than comment). But this should be enough to get you going if you're prepared to have fun with dynamic SQL.

Broadly speaking, I'd expect it to look something like this

-- Assume @PivotColumnList has been defined previously - these are the columns in the pivot

DECLARE @InsertSQL nvarchar(max); -- Used for dynamic SQL

DECLARE @InsertColumnList nvarchar(1000); -- List of columns in the table to be inserted to

-- Then dynamically set your insert columnlist e.g., 
-- '[Column_1], [Column_2], [Column_3], [Column_4], [Column_5], [Column_6], [Column_7]'

SET @CustomSQL = 
N'WITH YourPivotCTE AS
        (-- yourPivotcommand
        SELECT ...
        PIVOT ...
            (...
            FOR ... IN (' + @PivotColumnList' + N)
            ) AS pvt
        )
    INSERT INTO #tempTable ([Id], [Name],' + @InsertColumnList + N') 
        SELECT [Id], [Name], ' + @PivotColumnList + N'
        FROM YourPivotCTE;'

EXEC (@CustomSQL);