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 you...

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 and...

You only have [FINAL_DATA_ENTRY].[DBO].Journal in your FROM clause. You need to join it to [FINAL_DATA_ENTRY].[DBO].[TRADETABLE].

I don't know the join type but something like

INSERT INTO Tradetable (Journal_No)

SELECT Journal.JournalNo

FROM ...

Thank you for the update.

It's always nice to have someone posting an update to his question even though the issue is not completely resolved (yet).

Change

PARTITION BY name

to PARTITION BY id,name

to get the cities numbered for each id.

Should help a little... 😉

gregory.anderson (4/16/2010)

---

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...

All I can think of is to use the undocumented sp_foreachtable (google for details) which really is looping through the tables.

I'm not sure though, if this database design really is...

I would look at it the other way around:

I would update the columns that failed based on the validity check of the "previous" permits to a value other than 0...

Did you try the steps suggested in BOL (BooksOnline, the SQL Server help system)? Especially the steps/views described in section "troubleshooting [SQL Server], Database Mail"?

george.greiner (4/16/2010)

---

... Each client gets their own report so the business decision part is easy as I already know what that query / report will look like but I am...

@dachen:

Did you notice that there were almost 50 people reading your post and actually moved on?

But as soon as there are ready to use sample data available, you already have...

gregory.anderson (4/16/2010)

---

It might be required to catch the [PWDPermitConversionDataID] values and the column that failed for the rows holding bad values (using the OUTPUT clause applied to the error checking...

Something like this? I'm not sure if it will meet your requirement but it should get you started...

Side note: please notice how I did the setup of DDL and sample...

Would you mind providing a sample Excel file we can use (including the OPENROWSET statement you're using) and your expected output based on that sample data?

Also, would you mind...

gregory.anderson (4/16/2010)

---

I've been racking my brain on the very last step of this "previous" permit section...with just a yes or no, is it even possible to update the ConversionStatusPrevX fields...