A little late perhaps but here's an alternate:

DECLARE @tableA TABLE

(

vehicleID VARCHAR(5),

[timestamp] DATETIME,

...

Nice one Mark!

I can admit when I'm outclassed.

She ain't pretty, she ain't swift!

But she does have the advantage that she's a gift!

WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 )

...

Alas no help from the forum, but maybe my solution will ultimately help others. Basically I did 3 things to make this work:

1. Modified code from above (added "AND...

I was going to suggest this (similar to ColdCoffee):

update a

set @val1 = a.val1 = b.val1,

@val2 = a.val2 = b.val2

OUTPUT inserted.*, DELETED.*

from #temp2 a

join (

SELECT TOP 1...

This article seems to be relevant:

http://www.sql-questions.com/microsoft/SQL-Server-Programming/34302827/how-to-update-same-row-more-than-once-in-one-update-statement.aspx

Haven't found a way to return your option 2!

Quite peculiar behavior indeed!

By using a RIGHT JOIN you can make it return option 1. Try it with the OUTPUT statement I've included and you'll see that it seems...

In that case, I think you're going to need to use a MERGE.

Try this:

INSERT INTO #Destination

SELECT s.* FROM #Staging s

RIGHT OUTER JOIN #Destination d ON s.Account = d.Account and s.ReportDate = d.ReportDate

WHERE d.Account IS NULL

SELECT *

FROM #Destination

Let me ask, did you find some issues in your original counting process?

I was looking at that thinking there might be some cases that could trip it up, but actually...

Figured out another way to do it without using that weird sub-query! Thank god!

DECLARE @Checkin date = '2012-01-01'

DECLARE @Checkout date = '2012-01-04'

DECLARE @Checkoutin date

SET @Checkoutin = DATEADD(DAY,1,@Checkout)

--SELECT @Checkoutin

--SELECT @Checkout

DECLARE...

My comment was that it didn't work after I exploded the record set out to 50000 records with my CROSS APPLY INSERTs.

You may try it yourself if you don't believe...

I thought that might be what you wanted, but didn't try to make it happen.

I believe that I have done something like that before. I need to find that...

Sorry to be unclear. I referred to the query posted by Eugene.

First of all, I had to correct your DDL. The "ON [Inventory]" stuff was causing it to fail. This version ran for me.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING...