March 15, 2011 at 8:50 am
Hello All,
This may sound like a weird request, so I will explain as best as possible. Every table in my database has a bit column which is used to tell whether the data needs to be replicated or not. (This is done manual because of the business I am in and we only work across satellites, so the Replication Built into SQL does not work for us.) Anyway, I have a service that will run and pick up data from one place, email it, and then it gets extracted on other end. In both places, I need to reset the flag telling whether the replication has occurred. I would like to have only 1 procedure that I can send a table name, primary keys, and Row data and update the value.
Anyone have any idea whether:
a) this is even possible
b) how would i do it?
I have a procedure to get the primary keys based on a table name, but no idea how to build a procedure to take ANY table, keys, and data.
Any help would be greatly appreciated.
Thanks,
B.K.
March 15, 2011 at 10:07 am
This undocumented procedure (spMSforeachtable) might be what you need.
March 15, 2011 at 10:19 am
I'm thinking the other way around completely.
Get a "change log". Generate dynamic sql from there and exec.
No need to loop in all tables if only 1 table was changed.
March 15, 2011 at 10:24 am
Here's what I'd do, especially as you might need this later.
Get a sproc for every table that updates a field based on the PK.
That's a lot of writing, but you can build a script to help you do this. Use a script that loops over all tables, or Ron's sp_msForEachTable, and inside the loop, build a script.
The script should "Create procedure UpdateBitFor" + table_name, getting the table name from the looping. Find the PK from system tables, and use that as a parmater, and build the update statement inside the loop so you have a string at the end that looks like:
CREATE PROCEDURE UpdateBitForSales
as
update Sales
set bit = 0
or
CREATE PROCEDURE UpdateBitForSales
@pk = null
as
if @pk is null
update Sales
set bit = 0
else
update Sales
set bit = 0
where MyPK = @pk
You can also write a proc that then executes each of these procs. It will give you some reusable components you can call for updating single tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply