Ah. Got it... very nice work around and thanks for the clarification on the xp_/sp_.  The proc is actually listed as an sp_ in the Master DB but I guess...

I'm thinking that those extra services take up some of the memory that would normally have been available to SQL Server on otherwise identical machines so I'm thinking you're right.

Like I said earlier... the hard knocks code will beat the pants off DTS either way.  Thanks for the feedback Greg.  You can get some extra performance out of SELECT...

>>>These temp tables can then be cursored...

I don't think you understood... the whole idea is to get rid of cursors and other RBAR methods in SQL Server.

>>But for this type of operation, I still think xp_execresultset is the best solution.

The original post was for "Eliminating Cursors" so I have to ask... how do you use it...

Just my humble opinion but I need to clear up some of the myths associated with Table Variables...

First, table variables don't live in memory anymore than temp tables...

Thanks for coming back, John...

Yeah, I saw your original post where you said you had a table with all of the individual possible values.  Your solution looks as though it...

Greg,

I don't know if having the Primary Key on the last column will affect performance especially for CLUSTERED keys... I'd have to study the format in BOL a bit.

However,...

Gail,

You wrote "ps, I don't understand your comment about pushing into a table without a pk. Best insert performance is a when inserting into a heap with no indexes".

Normally I...

Then something else went wrong, Gail.  Perhaps there was a cross-join in the code.  Perhaps you had a bad connection (it happens).  Perhaps the linked server was not setup correctly. ...

Not sure why that would matter at all but OK, whatever you say.  I was thinking that maybe the servers had some different settings (like collation and memory allocation), a...

Uh... if you're new, it'll take a bit of study on your part, but you may want to consider setting up "replication"... then, it's nearly "auto-magic" from there.  The steps...

Well, thanks Greg.  I'm thinking that 35 seconds including the addition of a Primary Key isn't too bad (1.37 million rows per minute).  I love it when DTS looses (and it...

If you truly want to synchronize, why not just generate all the code for sprocs, views, and udf's in Enterprise Manager and let 'er rip?

Ok, then... you ask a question, I bust a hump giving you a pretty good answer... did it work for you or what?