Update a table based on flags from other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update a table based on flags from other table

Author

Topic

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-26 : 11:43:09

I have a table "Table1" which I need to update based on a flag from a config table, "Table_Config". The column names on both the tables are same. The two tables look as shown below.

Table1:

Primary_Key		Col1		Col2		Col3		Col4
----------------------------------------------------------------------------
ABC123			20120731	14		Microsoft	100.00
CDE456			20120731	14		IBM		250.00
URYT23			20120731	14		Oracle		150.00

Table_Config:

Primary_Key		Col1		Col2		Col3		Col4
---------------------------------------------------------------------------------
UpdateFlag		N		N		Y		Y

I have an existing stored procedure that updates the columns on "Table1" by accepting the column name as parameter, something like
Exec usp_UpdateTable "Col1"
Exec usp_UpdateTable "Col2"
Exec usp_UpdateTable "Col3"

I need to call the above stored procedures only when the flag for a particular column on "table_Config" is "Y".

I am planning on implementing this by following the below approach:

- Get all column names from "Table1" and stored in a temp table. The table will now have "Col1", "Col2", "Col3", etc as rows.
- Use a cursor or a while loop to read through the rows in the temp table. This will now return "Col1", "Col2", "Col3", etc.
- For each row, check if the Flag is "Y" by reading "Table_Config", if yes call the stored procedure, Exec usp_UpdateTable <column name>

Please let me know if you guys think of a better approach.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 11:54:31

You could do something like shown below. But, a better alternative might be to modify the stored proc to update only if the config_table has a Y for that column. Also, if you do it that way, it may be possible to update all the columns in one call rather than calling the stored proc (and the underlying update) multiple times.

DECLARE @config VARCHAR(32);

SELECT @config = configColumn FROM table_config WHERE ColumnName = 'Col1';
IF (@config = 'Y') EXEC usp_UpdateTable 'Col1'

SELECT @config = configColumn FROM table_config WHERE ColumnName = 'Col2';
IF (@config = 'Y') EXEC usp_UpdateTable 'Col2'

johnconstraint
Starting Member

23 Posts

Posted - 2012-10-26 : 12:16:23

Thanks sunitabeck. The update stored procedure is been existing for a while and being called from different places, so I am being asked to not touch that.

I forgot to mention one thing in my original post (sorry about that!) - the "Table1" and "Table_Config" has about 25 columns, thats the reason I thought it was a good idea to use a cursor or a while loop to process through all columns instead of having individual sql statements to check the flag.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources