quote:

---

Originally posted by pvccaz

Hi,

I have a table called ListTable that has a list of table names, column names and Values.

Example of the ListTable:

TABLE_NAME COLUMN_NAME Value
Table1 Column1_email Test@email.com
Table1 Column2_email Test@email.com
Table2 Column1_firstname SteveFName
Table2 Column2_LastName TurnerLName

I would like to generate a sql statement that reads the ListTable and generates an update statement using the columns TABLE_NAME, COLUMN_NAME and Value also executes it.

Basically it should generate the below statements and execute it.

UPDATE Table1 SET Column1_email = 'Test@email.com'
UPDATE Table1 SET Column2_email = 'Test@email.com'
UPDATE Table2 SET Column1_firstname = 'SteveFName'
UPDATE Table2 SET Column2_lastname = 'TurnerLName'

---

---

Is there any dynamic sql method that could be used to acomplish this ?

Any help would be much appreicated.

---

SELECT
	'UPDATE ' + TABLE_NAME + ' SET ' +
	COLUMN_NAME + ' = ' + VALUE + ';'
FROM
	YourTable;

SELECT
	'UPDATE ' + TABLE_NAME + ' SET ' +
	COLUMN_NAME + ' = ''' + VALUE + ''';'
FROM
	YourTable;

quote:

---

Originally posted by pvccaz

Thank you James and Madhivanan !

I would like to be able to run dynamically the query without copying to SSMS. So, I modified the query as follows:

DECLARE @sql nvarchar(MAX)
SET @sql =
(
SELECT
'UPDATE ' + TABLE_NAME + ' SET ' +
COLUMN_NAME + ' = ''' + VALUE + ''';'
FROM
ListTable
)
EXEC sp_executesql @sql
GO

I am getting an error message :
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

There are multiple records in the ouput - but they need to be executed. I am not sure how to fix this.

Thanks

---

DECLARE @sql nvarchar(MAX)
SET @sql = 
(
SELECT
	'UPDATE ' + TABLE_NAME + ' SET ' +
	COLUMN_NAME + ' = ''' + VALUE + ''';'
FROM
	ListTable FOR XML PATH('')
)
EXEC sp_executesql @sql
GO