Dynamic update statement

• legeboka

  Mr or Mrs. 500

  Points: 558

  More actions

  February 27, 2018 at 7:31 am

  #343736

  Good morning,

  I’m trying to figure out if it’s possible to create dynamicupdate statement. Structure of the table #TempDetails can change and because ofthat I don’t want to hard code column names. @tempChangeRec table is dynamic andvalues from this table should be populate to #ExcpectedOutcome table

  Code below should be self-exploratory

  Thank you

   

  IF (SELECT OBJECT_ID('tempdb..#tempChangedRec'))is not null

      DROP TABLE #tempChangedRec

  CREATE TABLE #tempChangedRec

         (PK INT IDENTITY(1,1) NOT NULL,

         RecID VARCHAR(15),

         ColumnName VARCHAR(15),

         NewValue VARCHAR(15))

   

  INSERT INTO #tempChangedRec (RecID, ColumnName,  NewValue)

  SELECT 'T1', 'C', 'G'

  UNION ALL

  SELECT 'T1', 'Rep1', NULL

  UNION ALL

  SELECT 'T1', 'Rep2',  'abc'

   

  --SELECT* FROM #tempChangedRec

   

  IF (SELECT OBJECT_ID('tempdb..#TempDetails'))is not null

      DROP TABLE #TempDetails

  CREATE TABLE #TempDetails

  (

  RecIDvarchar(15),

  A varchar(15),

  B varchar(15),

  C varchar(15),

  Rep1varchar(15),

  Rep2varchar(15),

  Rep3VARCHAR(15)

  )

   

  INSERT INTO #TempDetails (RecID,  A,   B,  C, Rep1, Rep2,  Rep3)

         SELECT 'T1', 'W','W','Q', '400E','TEST','RRR'

         UNION ALL

         SELECT 'T2','E','Y','P','23W','23W',NULL

   

  --SELECT* FROM #TempDetails

   

  IF (SELECT OBJECT_ID('tempdb..#ExcpectedOutcome'))is not null

      DROP TABLE #ExcpectedOutcome

  CREATE TABLE #ExcpectedOutcome

  (

  RecIDvarchar(15),

  A varchar(15),

  B varchar(15),

  C varchar(15),

  Rep1varchar(15),

  Rep2varchar(15),

  Rep3VARCHAR(15)

  )

   

  INSERT INTO #ExcpectedOutcome (RecID,  A,   B,  C, Rep1, Rep2,  Rep3)

         SELECT 'T1', 'W','W','G', NULL,'abc','RRR'

         UNION ALL

         SELECT 'T2','E','Y','P','23W','23W',NULL

   

  SELECT * FROM #tempChangedRec

  SELECT * FROM #TempDetails

   

  PRINT 'PIVOT table #tempChangedRec toget columns'

  DECLARE @strCreateTable NVARCHAR(MAX)

  DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX), @colsWithDT NVARCHAR(MAX);

  IF OBJECT_ID('dbo.Prep', 'U') IS NOT NULL

          DROP TABLE dbo.Prep

  SET @colsWithDT= STUFF(

                   (

                       SELECT DISTINCT

                              ','+QUOTENAME(c.[ColumnName]) + ' VARCHAR(100) '

                       FROM #tempChangedRec c FOR XML PATH(''), TYPE

                   ).value('.', 'nvarchar(max)'), 1, 1, '');

  SELECT @strCreateTable  ='CREATE TABLE dbo.Prep (DRAW_NO VARCHAR(55),  ' + @colsWithDT + ')'

  EXEC sp_executesql @strCreateTable

  --SELECT* FROM Prep

   

  SET @cols = STUFF(

                   (

                       SELECT DISTINCT

                              ','+QUOTENAME(c.[ColumnName])

                       FROM #tempChangedRec c FOR XML PATH(''), TYPE

                   ).value('.', 'nvarchar(max)'), 1, 1, '');

  --PRINT@cols

   

  SET @query   = 'SELECT RecID,  '+ @cols + '

                                                 FROM(

                                                       SELECT[RecID],    NewValue,   ColumnName

                                                                           FROM#tempChangedRec

                                                               )x pivot (min(NewValue ) for ColumnName in ('+@cols+')) p';

  EXECUTE(@query);

   

  --Needdynamic update statment

  --UPDATE#ExcpectedOutcome SET  C=...   FROM @query Above

  PRINT 'Expected outcome after update'

  SELECT * FROM #ExcpectedOutcome