Krishna1 (4/4/2013)

---

Dear All

I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very slow.

How do i find out whats the issue.

In...

Arjun Sivadasan (4/4/2013)

---

I had some free time at work and was trying out some silly queries when I came across this.

CTE referenced in a subquery does not yield any result....

I think it is an iTVF

I don't think that matters much,...

the sqlist (4/3/2013)

---

vliet (4/3/2013)

---

We tried both the CTE approach and the AND clause on the WHEN NOT MATCHED BY SOURCE part to merge rows spanning only a certain period. The...

I think Dwain knows a thing or two about table constructors...

Can you post the actual execution plan for this please, as a .sqlplan attachment:

SELECT TOP 250000

[z13031322175522447287ca90] = x1.wo_ref,

[z13031322175522447289ca90] = SUM(x.approved_ts_amount)

FROM dbo.wo x1

INNER JOIN dbo.wocc x3

ON x1.wo_id...

Does it meet the requirements for Step 1? The accumulation would be Step 2.

Anytime, thanks for the feedback.

I don't drink water, fish frolic in it.

The joins are all messed up. It runs forever because it's generating a partial cartesian product. Try this instead:

SELECT

u.Useraccount_First_Name,

u.Useraccount_Last_Name,

x.*

FROM Application_Useraccounts u -- "driving" table is now correct

CROSS APPLY...

Does the SELECT without the INSERT output the correct result set?

Use MERGE with an output clause. Dwain Camps has an excellent article on the front page today which describes a potential pratfall issue with MERGE. It's perfectly adequate to get...

Use the LIKE operator:

SELECT *

FROM Table1 t1

INNER JOIN Table2 t2

ON t2.Region LIKE '*'+t1.Region+'*%'

SELECT *

FROM Table1 t1

INNER JOIN Table2 t2

ON t2.Region = '*'+t1.Region+'*'

Thanks, it's so much easier when you have sample data to play with.

As Kevin pointed...