How to add top 1 row as header on SQL server as extra row ?

Question

I work on SQL server 2014 I face issue

but I don't know how to solve it

I need to add columns header as first row on table

I Try as below but i get error

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

what i have tried

CREATE TABLE #AllData  
(  
  
PartID INT,  
Code VARCHAR(20),  
CodeTypeID INT,  
RevisionID BIGINT,  
ZPLID INT,  
ConCount INT,  
FeatureName nvarchar(500),  
FeatureValue  nvarchar(500)  
  
)  
insert into #AllData VALUES(2020,'ab5060',877491,26553312,4125,10,'Heat','IC')  
insert into #AllData   
SELECT STUFF((  
            SELECT ',' + CAST(name AS VARCHAR(50))  
            FROM (  
                SELECT name  
                FROM tempdb.sys.columns  
WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')  
          
                ) k  
            FOR XML PATH('')  
            ), 1, 1, '')  
  
SELECT * FROM #AllData  

expected result as below

Answer 1

Check this example:

drop table if exists #AllData

CREATE TABLE #AllData
(
    PartID INT,
    Code VARCHAR(20),
    CodeTypeID INT,
    RevisionID BIGINT,
    ZPLID INT,
    ConCount INT,
    FeatureName nvarchar(500),
    FeatureValue  nvarchar(500)
)

insert into #AllData VALUES(2020,'ab5060',877491,26553312,4125,10,'Heat','IC')

SELECT * FROM #AllData

declare @s varchar(max) = ''

set @s =
    concat( 
    'select ',
        STUFF((
            SELECT ',' + quotename(name)
                FROM tempdb.sys.columns
                WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
            FOR XML PATH('')
            ), 1, 1, ''),
    ' from ( select ',
        STUFF((
            SELECT ',' + cast(quotename(name, '''') AS VARCHAR(50)) + ' as ' + quotename(name)
                FROM tempdb.sys.columns
                WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
            FOR XML PATH('')
            ), 1, 1, ''),
        ', g=0 union all select ',
        STUFF((
            SELECT ',cast(' + quotename(name) + ' AS VARCHAR(50))'
                FROM tempdb.sys.columns
                WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
            FOR XML PATH('')
            ), 1, 1, ''),
        ', g=1 from tempdb..#AllData) t order by g' )

print @s

exec (@s)