>>we re-indexed just to be sure,

Speaking of indexes, does every table have a clustered index ? Rebuilding non-clustered indexes doesn't solve fragmentation in the underlying table data pages if...

Setting aside the ORDER BY issue for a moment, this query is a performance nightmare wiating to happen.

Sub-SELECTs within a SELECT create a cursor-like slug of an execution plan.

You need...

Also check max memory setting:

exec sp_configure 'show advanced options', 1

go

reconfigure

go

sp_configure 'max server memory'

Again, you need to get out of the one-row-at-a-time procedural mindset, and lose the IF ... THEN.

Set based approach, using WHERE EXISTS to determine if the key already exists prior...

Compare this to the 'template' in my 1st reply.

UPDATE AP -- Update the alias name

SET 

  -- Columns being updated are not prefixed by tablename or alias

  DataDate =...

UPDATE tblAPData

SET tblAPData.DataDate = tblAPData.DataDate = T.DataDate

2 equal signs ?

Set based solution, single SQL statement with no looping:

INSERT INTO ContactMoreInfo (ContactID, Category)

SELECT ContactID, 'Gift2006'

FROM   ContactMoreInfo

WHERE Category = 'Gift 2005'

UNION ALL

SELECT ContactID, 'Marketing Mailing List'

FROM   ContactMoreInfo

WHERE Category = 'Gift 2005'

>>Is WHILE EXISTS the wrong construct for this?  Why does the script above loop eternally?

If a loop is based on a certain condition (EXISTence of something) and you do nothing...

Didn't notice that condition. In that case you need to incorporate a derived table (I named it "dtMostRecent") to allow selection of only the latest row per keyset.

INSERT INTO Target

 ...

>> that seems like it's just giving a row count, which is not what I need to do at all.

Your sample code did this:

>>SET @MyCounter = @MyCounter + 1

Which is counting...

You are going to have a very difficult time as DBA if you can't drop procedural language based looping concepts and approach SQL via SET based operations.

For your example...

Typically:

-- Create new records where the key does not exist

INSERT INTO (column list)

WHERE NOT EXISTS (

  SELECT * FROM Target

  WHERE Target.Primarykeys = Source.PrimaryKeys

)

-- Update existing records where the key...

>>I am trying to code out all derived tables and temp tables from my SP

Why ?

Derived tables exist for a reason. Sometimes they are the optimal or only solution.

SELECT...

>>UPR00200 is employee address table, one employee can have more than 1 address.

You haven't provided enough requirements to enable a solution.

If an employee has more than 1 address, and you...

Which version & edition of SQL Server and which operating system ?

Can't go above 2GB on certain combinations - eg the /3GB switch is meaningless on Win2000 Standard and only works...