Post reply

Single Procedure To Update One Column in Any Table

  • March 15, 2011 at 8:50 am

    #235161

    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

    #1298954

    This undocumented procedure (spMSforeachtable) might be what you need.

    http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

    http://weblogs.asp.net/nunogomes/archive/2008/08/19/sql-server-undocumented-stored-procedure-sp-msforeachtable.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 15, 2011 at 10:19 am

    #1298960

    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

    #1298965

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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