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

ahmed salah 3,126 Reputation points
2021-11-17T19:15:15.21+00:00

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

150294-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,493 questions
No comments
{count} votes

Accepted answer
  1. Viorel 82,646 Reputation points
    2021-11-17T19:43:37.99+00:00

    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)
    
    No comments

0 additional answers

Sort by: Most helpful