Insertion failed when converting the nvarchar value to data type int in sql server

userGXjI 0
Viewed 47

I have a XML transposing rows into column query. I am trying to insert the data to a table that is created dynamically. However, I am getting error saying conversion failed when converting nvarchar value to data type int when I execute '@query'

This is my current code:

DECLARE @cols NVARCHAR(MAX), @cols1 NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
             (
                 SELECT 
                        ','+QUOTENAME(c.[destfieldname] ) + c.datatype
                 FROM #specnorm c 
                 left join #rawtemp d on c.fieldname = d.fieldname
                 group by c.destfieldname, c.datatype
                 order by min(sourceSequenceTypical) 
                 for xml path ('')),1,1,'')+ ', [sequenceID] int, [subsequenceID] int';


SET @cols1 = STUFF(
             (
                 SELECT 
                        ','+QUOTENAME(c.[destfieldname] )
                 FROM #specnorm c 
                 left join #rawtemp d on c.fieldname = d.fieldname
                 group by c.destfieldname
                 order by min(sourceSequenceTypical) 
                 for xml path ('')),1,1,'')+ ', [sequenceID], 
                 [subsequenceID] ';


 SET @query = ' SELECT '+@cols1+' from (SELECT 
       c.[rownumber],
       c.[contents],
       d.[destfieldname] 
FROM #rawtemp c
left join #specnorm d on c.fieldname = d.fieldname
)x pivot (max(contents) for destfieldname in ('+@cols1+')) p'


exec ('CREATE Table dbo.sample' + ' ('  +@cols + ')');
exec ('Insert into dbo.sample' + @query)
bulkinsert sql-server sql
1 Answers

try:

SET @query = 'SELECT '+@cols+' into YOURTABLE from (SELECT 
   [rownumber],
   [contents],
   [fieldname] 
FROM #rawtemp
) x pivot (max(contents) for fieldname in ('+@cols+')) p'

it creates YOURTABLE on the fly

userTMI20
Related