Had to make a slight correction to my prior post and add a question.

Mark-101232 (8/29/2012)

---

If you're willing to jump through a load of hoops, you can update both tables with a view

CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1)...

Yeah, so now what I haven't tested it with is:

Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also then...

ChrisM@Work (8/29/2012)

---

Why not? Seems very unreasonable.

You're using SQL Server 2008...

You could UPDATE the first table and have an UPDATE TRIGGER on it that UPDATEs the second table.

But I believe that a transaction is the way to go.

You might want to try this one also:

UPDATE t

SET Flag = CASE (SELECT 1 FROM Table2 WHERE number = NEW_NUMBER) WHEN 1 THEN Flag ELSE 0 END

FROM Table1 t

INNER JOIN...

ChrisM@Work (8/29/2012)

---

dwain.c (8/28/2012)

---

I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:

CREATE...

How do you know that abc belongs in col3 and pqr belongs in col5?

So let's really try to get clever (or really obnoxious depending on your perspective :-D) and allow for up to 9,999,999 INSERTs per day. Try this:

CREATE TABLE #t (ID...

Jeff Moden (8/28/2012)

---

dwain.c (8/28/2012)

---

I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his...

I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:

CREATE TABLE #t...

Look at the RANK() and DENSE_RANK() window functions.

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

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

I believe that the answer is no.

Business logic should be applied prior to the data being INSERTed. Triggers are more for stuff like:

- Suppressing updates to certain columns

- Creating...

Well, these are the Vampire_Retailers now aren't they?

Loved that one Joe!

LEFT JOINs, CROSS JOINs, FULL OUTER JOINs? What's up with that?

Why not something a bit simpler like this?

;WITH CTE AS (

SELECT n=1, DocNum, Ref, Seq

...