Update multiple tables

Question

Update multiple tables

manikandan-1115962

Old Hand

Points: 300
More actions
July 15, 2009 at 2:52 am

#136522

how to Perform update in multiple tables using single sql query

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

VaibhavChoubey@SQL Ten Centuries Points: 1033 More actions · Answer 1

You can use dynamic sql to solve your problem, take all tables u want to update in a temp table and put your dynamic sql query in a loop changing value of table name(placed as a variable)

Grant Fritchey SSC Guru Points: 398717 More actions · Answer 2

There is no way to update multiple tables from a single TSQL statement. If you want to update multiple tables, you will need multiple statements. I'm not sure that you'd need to create dynamic statements within a loop to accomplish this. You could simply write a series of update statements within a procedure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kramaswamy SSCoach Points: 18181 More actions · Answer 3

manikandan (7/15/2009)
how to Perform update in multiple tables using single sql query

There is one other approach you can use, and that's to use the system stored proc sp_MSforeachtable. It's a bit complex, but it works pretty well if you have a large number of tables that all need to be updated in the same fashion, like, for example, if you wanted to add an identity field to all the tables in your database since none had them.

There's a decent article on this stored proc

here.