Home Update a declared tabled dynamic column name
Reply: 2

Update a declared tabled dynamic column name

oldman
1#
oldman Published in 2018-01-12 13:04:14Z

I am running a SQL loop to update a declared table. Please have a look at the following

DECLARE @tblResults TABLE (  [idx]              INT IDENTITY(0,1)
                            ,[cityCode]         VARCHAR(3)
                            ,[month_1_perc]     INT
                            ,[month_2_perc]     INT
                            ,[month_3_perc]     INT
                            ,[month_4_perc]     INT
                            ,[month_5_perc]     INT
                            ,[month_6_perc]     INT
                            ,[month_7_perc]     INT
                            ,[month_8_perc]     INT
                            ,[month_9_perc]     INT
                            ,[month_10_perc]    INT
                            ,[month_11_perc]    INT
                            ,[month_12_perc]    INT)

loop will start here

DECLARE @colName VARCHAR(15)
DECLARE @sqlExec VARCHAR(500) = CONACT('UPDATE @tblResults SET [month_', @colName, '_perc] = 9; 

EXECUTE(@sql)

loop

This obviously doesn't work - I am trying to find a work around if any - Open to suggestions

selami
2#
selami Reply to 2018-01-12 13:15:12Z
DECLARE @colName VARCHAR(15)
DECLARE @sqlExec VARCHAR(500)

SET @colName = '1'
SET @sqlExec = 'UPDATE @tblResults SET [month_' + @colName + '_perc] = 9' 

EXECUTE( @sqlExec)
Veljko89
3#
Veljko89 Reply to 2018-01-12 13:25:57Z

I am a bit confused with your request, but if you really want to use a loop

declare @i int = 1;
declare @sqlUpdate nvarchar(max) = 'UPDATE tblResults SET ';
declare @sql;

while @i <= 12 
BEGIN

set @sql = @sqlUpdate + '[month_' + cast(@i as nvarchar(5)) + '_perc] = 9;' -- value?

print (@sql)
-- execute (@sql)

set @i = @i + 1;
END

Or you want to set same value to all columns in one go

declare @cols nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(col.name) + ' = 9' -- value?
                      from tempdb.sys.columns col
                        join tempdb.sys.tables tab on col.object_id = tab.object_id
                        where tempdb.tab.name = 'tblResults'
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')  


declare @sql nvarchar(max) = 'UPDATE tblResults SET ' + @cols;

print (@sql)
-- execute (@sql)

Note, none of scripts are tested

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299351 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO