SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update a table based on flags from other table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnconstraint
Starting Member

Canada
23 Posts
Posted - 10/26/2012 :  11:43:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/26/2012 :  11:54:31  Show Profile  Reply with Quote
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'
Go to Top of Page

johnconstraint
Starting Member

Canada
23 Posts
Posted - 10/26/2012 :  12:16:23  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000