January 9, 2015 at 10:56 am
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hope that makes sense. Appreciate the help.
January 9, 2015 at 12:10 pm
JP10 (1/9/2015)
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hope that makes sense. Appreciate the help.
Quick thought, what about filtering for the update, certainly you don't want to update all records every time?
😎
January 9, 2015 at 12:17 pm
I'm not concerned about the records I'm updating in the update statement, I'm trying to get the actual update statement to be constructed dynamically and have it execute. Hope that clears things. Thanks.
January 9, 2015 at 2:42 pm
DECLARE @sql varchar(max)
DECLARE @column_list varchar(max) --as returned by first query
SET @column_list = 'age,sex,race' --for example
SET @sql = 'UPDATE tbl2 SET col2 = [' + REPLACE(@column_list, ',', ']+[') + ']'
PRINT @sql
--EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 14, 2015 at 12:42 pm
Thanks Scott, I think I got it. I did what you did, but I used a pivot statement to get my rows of column names as columns so can store them into variables separately.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply