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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 17, 2021 at 7:07 pm

#3954174

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 :

PartIDCodeCodeTypeIDRevisionIDZPLIDConCountFeatureNameFeatureValue
PartIDCodeCodeTypeIDRevisionIDZPLIDConCountFeatureNameFeatureValue
2020ab506087749126553312412510HeatIC

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 1

From looking at what you have, I think I see what is wrong. You are inserting into AllData the columns, but since you are not specifying a column to insert into, it is going into column 1. Column 1 is PartID which is an INT. You are trying to insert a VARCHAR into an INT column and that is failing.

What you will need to do if what you have is a hard requirement for your output is to convert all of your columns from the proper data types to VARCHAR as you are going to be inserting VARCHAR's into the table as column names are VARCHAR types. Next, you are going to want to insert the column names into the table in their appropriate columns. Finally, you insert the data for the table.

Personally, since you are creating the temp table above, I would just insert the static values for the table columns rather than insert from sys.columns. But I also know that is just an example, so using sys.columns may be required.

Now, a different approach - add the column names from the application side rather than the database side. .NET has all of the column names when it pulls the data from the database, so you can access all of the column names from there. The advantage to this is that your data will be in proper datatypes (INTs for example). Otherwise, you will need to convert all of the data to the proper datatypes before you can work with them.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

WHY do you need to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)