Holy moly. Referring back to my statement, after having seen this code, I wish to refine my original estimate.

Nope. Won't scale. Next.

BTW, I reformatted the query so I could actually...

Well, first, seperate derived tables & CTE's from temp tables & table variables. They are totally different critters. Derived tables & CTE's are constructs that exist within a query statement...

Well, it depends. If you have a TSQL script open, it should work just fine (just tested it to reconfirm). If you don't have a script open, the replace window...

I'm not the admin expert that configures our servers, but I do know the guy who does it sets the memory allocation to a fixed amount. Dynamic memory is primarily...

Can you post the full execution plan? You might be hitting the scan because of other things within the query, such as performing functions on the columns then using them...

I'm not sure, but it sounds like you might be talking about execution plans? These show what the optimizer and query engine will do with the code that you wrote....

Yeah, it only takes one row.

Using my code sample

UPDATE [ATI].[dbo].[IV00101]

SET ITEMDESC = x.ITEMDESC

,ITMSHNAM = x.ITMSHNAM....

FROM [AMCHR].[dbo].[IV00101] AS x

WHERE x.[ITEMNMBR]=[ATI].[dbo].[IV00101].[ITEMNMBR]

AND x.ITEMDESC IS NOT NULL

fred (2/19/2008)

---

I am having a little problem with my update query. I get the following Error msg.

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column...

I would not have more than one project for a given database. You should be able to manage a single database all the way from Dev through to Production from...

Yeah, I wouldn't create two data structures for the same data. I'd store the "messy" data right along side the "approved" data but then simply have a clean-up process that...

I've got a core, basic, set of counters that I suggest using here. They barely scratch the surface, but they ought to get you started.

If you do create a common set of code that you want to access from other databases, I wouldn't put it in master or msdb. You never know when a...

Also, not related to the issue you're describing, but worth mentioning, you're performing a non-ansi join between the two temp tables at the end of your query there.

FROM temp#1 x,temp#3...

If I understand the question correctly, no I wouldn't duplicate the structures & move the data around. Instead, store the member data in whatever format you had planned. Inlcude a...

The sample query you're showing looks correct. Verify the data in the original columns. Double-check your actual query (I'm assuming you're posting a sample and you have a real query...