For a single value from OUTPUT, I'm pretty sure it's free.

Where you might see a performance hit (or benefit, depending on how you define it) is when you define...

Garadin (11/25/2008)

---

The note about clustered indexing brings to mind something I came across in a section mockup yesterday. When making Foreign key reference tables (IE. Tying an employee to...

That one was a hoot. The original problem definition was what, four lines. It grew to almost four pages of code by the time the guy was done adding things.

Wow! Thanks!

You're very welcome.

Lookup information on sys.dm_exec_cached_plans and some of the other dynamic management views. I left that out and wish I had it in there now.

Do what Steve says....

Wow, I really misunderstood then. I thought you were going for the most frequent occurence.

Well then, here's one more suggestion. In the sub-query, replace the MAX with a TOP 1...

kalikal (11/25/2008)

---

Hi Grant

I had to make a slight change but it now works - thank you for taking the trouble to help.

WITH X AS (SELECT ...

Books Online are available on line here:

http://msdn.microsoft.com/en-us/library/ms130214.aspx

The OUTPUT clause that you want is here (this in the 2008 version, but it's the same):

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Here is an article describing the OUTPUT clause:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

@@IDENTITY...

Yes, it is possible through SSIS.

Oh, that's a different problem. I got you a max for the unique combination of values. Just to get a max for each ProductId drop the second sub-select in the...

I've never thought about automating it before. I took a quick look at what happens in Profiler when I script a database. It must be an SMO call from Management...

kalikal (11/25/2008)

---

I am trying to pull out the most occurring of productID and UnitID

So from the table below I want

ProductID UnitID

1 ...

I gave him the answer.... Yes

Yes, SSIS or BCP can both work with semi-structured data.

From the information you've given, you must have more than one row that meets the criteria defined. It's the only way you would get the problem.

The question intrigued me, so I played with until I got the right answer. I've no doubt (at all) that there are more efficient ways to work this, but this...