Matt's nailed it. If you went for the identity value on the table, you could get someone else's insert value. OUTPUT pretty much guarantees what you're getting. Still, in most...

Huh... I'm kind of amazed that works. I just tested it. Not only can you do it, but it doesn't affect inserts or updates. Strange.

No, I can't imagine why you...

You can also open up the Properties window and set it there.

Best of all, try using TSQL. I think it's a heck of a lot easier to create tables there...

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