Have you tried looking at the activity monitor of the source db (maybe the dest db as well)?

You can also run sys.dm_tran_locks to view the locks.

What happens when you copy the printed command and run it in command prompt?

Yup, you should put them all in a temp table so you can insert/update the result table all at once.

FYI,

Since my suggesting of while-loops, I ran across a few articles...

It's missing the FROM clause

SELECT @currClientID = ClientID FROM <your tb name> WHERE RowNum = @currRowNum

I didn't add the FROM clause because I didn't know what it was.

Hope this helps.

Thanks for the links.

I'm sure it'll give me some answers and hopefully more questions. 🙂

I'm assuming they mean "scalability" as well as "future feature additions"???

Unfortunately, I'm very very short on details. The discussion with my friend was more of us being curious/surprised about using...

Thank you so much for your insights.

I totally agree with everything you guys have said so far.

I think they wanted to have an architecture where any object (or table) can...

Oops, I think I misundertood your explanation...table 'a' and 'x' do not join...:-P

You can add a IF statement before the SELECT that returns data from table 'a'. This way 0...

Connect the data flow source to where table 'a' and 'x' reside (hopefully they are on the same server). Write a select statement that joins the two tables and returns...

Sorry, I can't provide a test scenario...

But from personal experience, I have seen performance improvements when rewriting slow cursor queries with while loops. And of course, vice versa. I'm afraid...

Although this propbably does not apply to this particular exercise...

FYI,

SELECT INTO will cause a schema lock, preventing other DDL statements from executing against the table.

If you don't care about schema...

Oops...copy and pasted too fast 😛

I, of course, meant to decrement @currRowNum.

Regarding the while loop suggestion, I was merely suggesting an alternate, potentially more memory efficient solution...not the best solution,...

Simple solution would be cursors that iterates through client ids...

If there are a lot of clients to retrieve payment histories for, and cursors are too slow, you can try a...

I guess I should have posted the complete query...

Well, if you are interested for future reference, here's link to a helpful tutorial.

For me, HAVING clause really came in handy when...

This link should answer your question

http://support.microsoft.com/kb/281122