UPDATE newTable

SET amount = oldTable.Amount

FROM [schema_name].[table_name] newTable INNER JOIN

[database_name].[schema_name].[table_name] oldTable ON oldTable.ID = newTable.ID

Not sure why, but ROW_NUMBER() is not recognized and I am...

Thanks 🙂 it's been good to learn from you!

I'm pretty much way off topic now, but do you think in reality there should be a 'compilation' step that refactors tvf's...

Yep - no worries.

Temp table: 1.218

Table variable: 0.921

I think probably what you wanted me to do instead was to change the 100 to 1000. Then it moves heavily in the...

That someone was me. and i did in...

Ahh I get what you were saying now. Sorry read it differently the first time. It's just where you had the code posted up that required a manually written statement...

Jeff Moden (1/6/2009)

---

Ok... sorry. Understood.

No, I didn't get your email. I have a pretty agressive spam filter and it sometimes eats the wrong things. Send me a...

Tom.Thomson (1/6/2009)

---

It can't be done though for table variables, since the only indexes that can be defined are those caused by UNIQUE or PRIMARY KEY constraints, so they are inherently...

No - it just gives you what was sent to the server - I wasn't 100% sure where you were coming from...

Jeff Moden (1/5/2009)

---

I've gotta agree with Kevin on the table variable thing. The only reason I'll ever use table variables is if I have to make a temporary storage...

Erm... that version doesn't actually do what's required does it? What happens if a new type other than 'Loan' or 'Insurance' is added?

Not that the schema makes that a change-free...

Jeff Moden (1/5/2009)

---

A clustered index and a PK, clustered or not, can both have multiple columns. As you well know (just saying it for folks who may not), you...

declare @sql varchar(4096)

set @sql = ''

select @sql = @sql + 'UPDATE [CustomerProducts] SET [RateVariance] = [RateVariance] + ' +

convert(varchar, [Variance]) + ' WHERE [' + Description + '] =...

As yoda would say 'sense your sql does make not - post table def and data you must. expected results. hmmmmmm'

TheSQLGuru (1/5/2009)

---

1) I see people do this regulary just out of habit and NOT use the ID column or use it in such a way that indexing isn't useful. ...