SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,446 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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)