SQL Server Update tables based on info in another table

kiayqfof 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(110)

I have table with information about another tables. Like this:

AccTableHistory :

| TableName| ColumnName  |
| -------- | ----------- |
| Table1   | FirstName   |
| Table1   | LastName    |
| Table1   | Adress      |
| Table2   | FirstName   |
| Table2   | SSN         |
| Table2   | FamilyStatus|

etc.

I need to write an update statement for each column from this list. Should be something like:

UPDATE Table1
SET 
   FirstName = 'Andrew'
  ,LastName  = 'Test'
  ,Adress    = 'Test'

But we have more than 100 tables and more than 800 columns in the list. I don't want to write an update for each table manually.

Is it possible to write a dynamic query or stored procedure to update each table from this list (AccTableHistory table)?

I have no idea how to achieve that

sql-server

来源：https://stackoverflow.com/questions/76666632/update-tables-based-on-info-in-another-table

1条答案

按热度按时间

mlnl4t2r1#

I did it like this:

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (
SELECT '
    UPDATE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ' ' + 'SET' + ' ' + c.name + ' ' + '=' + ' ' + 'TestValue'
FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.name IN (SELECT TableName FROM AccHistory) AND c.name IN (SELECT ColumnName FROM AccHistory)

    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
EXEC sys.sp_executesql @SQL

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Update tables based on info in another table

1条答案

相关问题

热门标签

最新问答