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

ahmed elbarbary

4y2.1k

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.

Expected result as below :

PartID	Code	CodeTypeID	RevisionID	ZPLID	ConCount	FeatureName	FeatureValue
2020	ab5060	877491	26553312	4125	10	Heat	IC

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 nameFROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')) kFOR XML PATH('')), 1, 1, '')SELECT * FROM #AllData

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar4y ago
Accepted answer
Refer below 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)
Sachin Singh4y ago
1. SELECT *
2. FROM tempdb.sys.columns
3. WHERE object_id = Object_id('tempdb..#AllData');
As the above query will return all column names as string, so you can't insert them as data, until you change all columns of your #Alldata to varchar.

Srinivasan Ramamoorthi4y ago

You have to declare all column types as varchar, since your first row is going to be column names.

Jignesh Kumar4y ago

What is expectation ?

Do you want to use this result in Report or on website to display on browser ?