lmu92 (4/22/2010)

---

Ok, let's try the following:

- after loading the PWDPermitConversionData2 table, add a clustered index on PWDPermitConversionDataID.

- after inserting into PWDPermitConversionErrors2, add a clusterd index on PWDPermitConversionDataID,RecordIndicator

- after inserting into...

How can the elapsed time be shorter than the CPU time?

I made most of those changes that you suggested.

Attached are the execution plans.

Here are the timings.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time =...

Ok, I have everything working now.

Doing the first and second inserts with a subqueries for each statement containing the pivoted rows, it took 2:36 the first try.

I then took the...

And when I comment those two case statements out and just put 2 @False values for those fields the insert works fine.

Edit: It's getting wierder (is that a word), when...

Hey Lutz, can you give me a hand with this? I have converted this over to one insert into the errors table for both primary and previous permits, and also...

Quick question, can you reuse CTE's in 2 separate statements inside a procedure?

lmu92 (4/20/2010)

---

gregory.anderson (4/20/2010)

---

If I read this right, we can go ahead and get rid of both the temp tables that we are using and just use the PermitsInserted table to...

lmu92 (4/17/2010)

---

When we started with c.u.r.s.o.r. stuff I didn't notice how similar the handling is. Now, seeing the set based code, it becomes more obvious.

Therefore, I'd recommend the next step:

If...

BTW Lutz, getting married, not graduating...

gregory.anderson (4/20/2010)

---

Ok, back to the fun...

How does this code block work (the MAX part) when it is going to return a non-integer value for the error mesage?

MAX(CASE WHEN Recordindicator =1...

Ok, back to the fun...

How does this code block work (the MAX part) when it is going to return a non-integer value for the error mesage?

MAX(CASE WHEN Recordindicator =1 THEN...

lmu92 (4/17/2010)

---

Ok, here's what I came up with:

I'm using a CTE again. The first one ("cte") is used to pivot the failed ConversionDataID's.

The second CTE ("cte_ri") pivot the inserted ConversionDataID's...

Hey guys, thanks again for everything. Just to let you know, I haven't read through Lutz's cte version of that last update yet, just read the first part of the...

Lutz, before I read your last post, I updated the Update statement for the previous permits, this will do the successful inserts, as well as the failed inserts all in...