"you'd wither away and waste to nothing if you didn't have your work"

No chance... I have other lifes, remember??

I'd go further than that. His understanding of relational database is wrong. The goal of a database is to hide the data retrival task/methods from the user. ...

"and you have multiple records on the inserted table what value is @intTreeID going to receive? the first, the second,...?"

...the last (just in case the newbies come up on...

Have you tried the single select approach??? I'm sure it can be the hell out of this insert select thingy...

Also you might wanna look at the identity property to create a next id automatically. This will speed things up.

Guys you forgot the most important question to ask :

Why do you want to use dynamic sql??

Did you ever read this?

The Curse and Blessings of Dynamic SQL

Nice quote, this one is ont step better : "Life is too short and good to work!" in fear"

Which would make sens because the local server executing the select could use the best plan instead of shipping all the data and letting the other server sort it out.

anyone got a huge table they can copy and drop to see the effects on the log?

R u talking to me??

The only way to track all those is to start a trace using the profiler. This will allow you to get all that information.

The downside...

I already said that, but he'll have to change the bot column to a tinyint to be able to index it.

Maybe something was recompiled, or rewritten in some way because you shouldn't get that big of a boost... moving from a sp to am inline function.

Ya that link is using DAO... not that much different from ADO but you still got to find the right method name .

doesn't validate that it ends with '0000' and it won't work for canadian/europian zip codes

Will be watching .

Now that I think of it I'm not that far away from the carpal tunnel stage...