We currently are in a debate on what is quicker, doing a query in a SP
that has Nth left outer joins or doing a insert followed by update
queries.  let me lay out a simple scenerio.  Please advise, the DBA
group is set on saying example 2 is better (they keep telling me Im
doing too many table scans).

Table "Store" has 1 Record

StoreNum: 1

Table "StoreDate" has many records that are associated to the Store
table; we will say 3 records fo this example

StoreNum: 1
DateType:1
DateValue: 1/1/2001

StoreNum: 1
DateType:2
DateValue: 2/2/2002

StoreNum: 1
DateType:3
DateValue: 3/3/2003




EXAMPLE 1

Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)

Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, SD1.DateValue As OpenDate, SD2.DateValue As
CloseDate, SD3.DateValue As LastInspectionDate
S.StoreNum = SD1.StoreNum And SD1.DateType = 1 Left Outer Join
StoreDate SD2 ON
S.StoreNum = SD2.StoreNum And SD2.DateType = 2 Left Outer Join
StoreDate SD3 ON
S.StoreNum = SD3.StoreNum And SD3.DateType = 3




EXAMPLE 2

Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)

Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, null As OpenDate, null As CloseDate, null As
LastInspectionDate
Update X
set opendate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 1

Update X
set closedate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 2

Update X
set lastinspectiondate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 3

Did you test it for yourself with SET STATISTICS IO ON?

You could easily improve Ex 2 by combining the three UPDATEs but I
would try something different from either solution:

INSERT INTO X (storenum, opendate, closedate, lastinspectiondate)
SELECT storenum,
  MAX(CASE WHEN datetype = 1 THEN datevalue END),
  MAX(CASE WHEN datetype = 2 THEN datevalue END),
  MAX(CASE WHEN datetype = 3 THEN datevalue END)
  FROM Store
  WHERE datetype BETWEEN 1 AND 3
  GROUP BY storenum ;

The examples are not equivalent.  The first example will insert the number
of rows in Store, leaving existing rows in the inserted table unaffected.
The second example will insert the same number of rows, but will update
EVERY row in the inserted table (that joins successfully).  I can't imagine
that the insert/update logic is "superior" in any way. Is there an
assumption that the inserted table is empty?

I'll ignore the potential logic flaws based on the design assumptions (e.g.,
store has a 1:1 relationship with storedate for rows where type = 3).  The
3rd datetime column named "LastInspectionDate" seems to contradict the
assumed 1:1 relationship.

As David indicated, you don't even need to join store to storedate to
generate the correct information (unless there is some "irregular" aspect to
your design/schema that was not mentioned).

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)