March 25, 2008 at 4:35 am
Hello all,
I have to update more tables in same query using SQL. But i dont know how many tables i have to update. So anyone help me to do this thing.
Or
think I already know I have over 700 tables to update. All tables have Emp_Id(Primary Key) and Emp_NIC. I want to update EMP_NIC to all the tables. But i cant hardcode table names. That is a my problem
Let me know anyone know to do this.
Thankyou all
March 25, 2008 at 4:54 am
sampathsoft (3/25/2008)
Hello all,I have to update more tables in same query using SQL. But i dont know how many tables i have to update. So anyone help me to do this thing.
Or
think I already know I have over 700 tables to update. All tables have Emp_Id(Primary Key) and Emp_NIC. I want to update EMP_NIC to all the tables. But i cant hardcode table names. That is a my problem
Let me know anyone know to do this.
Thankyou all
You cannot do this within a single query, but you can do it within a query batch. You'd need to cursor through each of the tables, identifying those that have an Emp_NIC and Emp_Id table and then writing a dynamic SQL statement.
However, if you want to update every table that has an EMP_NIC column then you could cheat and use sp_msforeachtable. The update against tables that don't have an EMP_NIC column will fail but all the others should succeed.
exec sp_msforeachtable 'update ? set EMP_NIC = '
I would suggest you test this on a test database first. If EMP_NIC is a string data-type you'll need to put two single quotes (four in total) around the value you want to update it to. Also, if EMP_NIC has a constraint on it this could complicate the matter.
Hope that helps.
March 25, 2008 at 5:21 am
Thanks Karl for your reply.
But i heared Cursurs are memory huter. So if i use cursors will it take more time or are there any othere way to do this
March 25, 2008 at 5:51 am
sampathsoft (3/25/2008)
Thanks Karl for your reply.But i heared Cursurs are memory huter. So if i use cursors will it take more time or are there any othere way to do this
Using cursors aren't ideal but I don't think there's another way around it in this case. You could use alternative methods that allow you to loop through each table but they'd pretty much amount to the same as a cursor.
If this is a one-time operation that you need to perform then I don't see a big problem with using sp_msforeachtable or developing your own cursor.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply