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 2000 Forums  Transact-SQL (2000)  Updating a table

Author  Topic  IreneKF Starting Member 1 Post Posted - 2007-04-23 : 15:46:48 I really hope someone can help me with this... I have a table of Relative Value Units (MasterTable) which is used to calculate productivity. Every year, I will receive a new version (UpdateTable): some rows will be unchanged, some will be new, some will no longer exist, and some will have a change in one or more of the columns. I have added 2 columns to MasterTable, and inserted the following in every row: EffFrom 1/1/2006 EffTo NULL The 2007 version arrived as a comma delimited text file. I imported it into UpdateTable (table structure identical to MasterTable) and inserted '1/1/2007' into the EffFrom column. Two columns are needed for the unique identifier: Code and Modifier. Unfortunately, the Modifier in most rows is NULL and is only valued when the same Code appears in more than 1 row. So there is no key on either table. Here is what I need to do: 1. If a row in UpdateTable is identical to a row in MasterTable (except for EffFrom), do NOTHING. 2. If UpdateTable.Code+Modifier does not exist in MasterTable, insert the row. 3. If UpdateTable.Code+Modifier = MasterTable.Code+Modifier, but at least one column has a different value, then a) set MasterTable.EffTo = '12/31/2006' b) insert the row from UpdateTable. 4. If a row in MasterTable has no match in UpdateTable, set MasterTable.EffTo = '12/31/2007'. There are 39 columns in each of the tables. How do I compare 2 tables on all columns EXCEPT EffFrom and EffTo? Also, any help with the coding would be much appreciated - this is a first for me. Thanks in advance! Irene