SQL Server – Executing Same SQL Statement for Different Tables

sql serversql-server-2008-r2t-sql

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table.

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table2 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

...

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table10 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

I'd like to rewrite this to use some kind of loop, so that the SQL is only written once. (This is for SQL Server 2008R2.)

The list of tables that need to be updated will remain static; if there are any changes to the list of tables to be updated, it would be due to an application upgrade, and I'd have to redetermine which tables need to be modified at that time. The list of tables to be updated will be placed into a temporary table.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

Best Answer

You dont need a loop for what you want. Dynamic SQL will help you want you want to achieve.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

--select * from #tablenames



declare @sqltext nvarchar(max) = N''
select @sqltext += 'UPDATE  desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM '+quotename(tablename)+ ' table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = '''';'+char(10)
from #tablenames  
order by tablename

print @sqltext

-- once you review the above print statement and are HAPPY, uncomment the below part. 
-- Below part will actually RUN the UPDATE statement ... 
-- exec sp_executesql @sqltext

Note: I agree with @AaronBertrand that you should create a physical table - that will be a driver table for the update statement and just update the table when you want to modify or insert more values if you want other tables to be updated.

Related Solutions

SQL Server – Find if Column Exists in Certain Tables

This will give you the Schema and Table names along with YES or NO and Creation date:

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'

How the master table works is unclear but it seems that adding such a WHERE clause should work:

WHERE t.name IN (
    SELECT tn.name + CAST(mn.id as varchar(10)) as name
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN [master] mn
)

This will mix the list of name with the list of ids from master. Names can be added to the list or it could be replaced by a Names table.

By the way, master is not a proper name for a table since it is also a system database name.

This query adds productname from sample @master table for id < 5:

declare @master table (id int, productname varchar(10))
insert into @master(id, productname) values(1, 'xxx'), (2, 'yyy'), (6, 'yyy')

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
    , m.productname
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
    SELECT name = tn.name + CAST(mn.id as varchar(10)) 
        , mn.productname
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN @master mn
    WHERE mn.id < 5
) m ON m.name = t.name
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'

Sql-server – Troubleshooting extremely long running query (SQL Server 2012)

After some excellent help from Kin, wBob, and Aaron Bertrand, I finally found my root cause: Table1 statistics needed updating.

I had already confirmed that statistics were updated 2 days prior during our weekly index maintenance, so didn't really think anything of it. However, I gave it a try, and that made the query run time go from 2hrs 30min down to 2 seconds!

Command run was:

UPDATE STATISTICS Table1;

Thanks for all the help, particularly in the article "The "Runaway" Query"

Best Answer

Related Solutions

SQL Server – Find if Column Exists in Certain Tables

Sql-server – Troubleshooting extremely long running query (SQL Server 2012)

Related Question