Using * to enforce the need to update both tables together I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired after a 12 year run! The current DBA is a bit mystified as to what he was trying to do here? A clue might be in the note on the former REMMED out code that apparently suddenly stopped working after several years?

Any ideas as to what this technique is trying to do as to "enforce the need to update both tables together"? The new DBA seems a bit mystified by what he was trying to do?

This is a copy of the Notation and the SQL 

-- the following intentionally uses * to enforce the need to
-- update both tables together

/* this stopped working!!  WHY?? 
Insert Into MAIN 
Select * 
From IPR_CIP  
Where IPR_CIP.CASE_NUM Not In 
  (Select CASE_NUM From MAIN); */


(The is the current deployed code:)

Insert Into MAIN 
Select * 
From IPR_CIP  
Where IPR_CIP.CASE_NUM In 
   (Select IPR_CIP.CASE_NUM
    From IPR_CIP
    Left Outer Join MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
    Where MAIN.CASE_NUM IS NULL);
I'm not entirely sure what he is trying to do either. This query is inserting into MAIN rows from IPR_CIP that don't already exist in MAIN. It depends on the two tables always having the same number of columns and corresponding columns having compatible data types. It would be much better to explicitly list the columns you are updating in MAIN and the columns you are selecting from IPR_CIP. The subquery is redundant and can be moved up into the parent. Here's the TSQL code I would use.

INSERT INTO MAIN (col1, col2, col3, ... , coln)
SELECT (col1, col2, col3, ..., coln)
FROM IPR_CIP
LEFT OUTER JOIN MAIN On IPR_CIP.CASE_NUM = MAIN.CASE_NUM
WHERE MAIN.CASE_NUM IS NULL