Here is what I get when running the three methods. Apologies for the somewhat sloppy code, I wrote this on my laptop while laying on the couch.

Jeff Moden (10/11/2008)

I ran your...

It was my understanding that these subqueries are basically the equivalent of derived tables, as they don't reference the outer rows and thus should not repeat for every row. ...

The IN clause is actually faster than the multiple joins. I tested it on the Northwind database (so it was easily repeatable) as so:

[font="Courier New"]

SELECT

            oh.orderid

            , od.productid

            , od.quantity

    FROM dbo.Orders oh

            JOIN...

He double posted.

http://www.sqlservercentral.com/Forums/Topic584195-338-1.aspx

Hah, you caught me there for a second. In your second post, you posted some data and an expected result. Then in your third, the actual inserts had...

Please refer to the post in my signature on how to post data on this forum and then post some sample data and your table structure.

Except he's not trying to make them uppercase, he's trying to make keywords(and only keywords) all lower. Hence the amount of outrage...

Go ahead and create your temp table, but then instead of a loop, use this:

[font="Courier New"]UPDATE table_name

SET    field1=T2.value1,

   field2=T2.value2

FROM table_name T1 INNER JOIN temp_table T2 ON T1.ID = T2.ID AND T1.RecNo...

Heh, I briefly thought about putting all of that into one huge string to exec... but that kinda thing just makes me a bit nervous. No practical reason for...

I was super excited about it, and while it may just be that I'm so used to coding without it that I can't appreciate it yet, so far it just...

Yeah... don't do what I posted, that's what I get for trying to respond real quick before I answered someone's email. That way will have you dropping all your...

Well, I spent a couple hours attempting to beat the speed of Jeff's method(not that I actually believed I could, but I figured a lot out about it while trying)...

SELECT REPLACE(CAST(col1 as varchar(20)) + CAST(col2 as varchar(20)),'0','')

Rather than updating 1000 SP's, couldn't you just create a view named what the table used to be that references the new database / table with a SELECT * ?