It might be more efficient to unpivot the data instead of comparing column by column.

You could either lookup UNPIVOT in BOL (BooksOnLine, the SQL Server help system, usually installed together...

It depends on the scenario.

There are cases where a table variable would be preferred over a temp table (e.g. to capture some values even after a rollback). Without knowing the...

Oh, now I see what you're looking for...

Maybe the following code will do the trick:

;WITH cte AS

(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ActivityControllerID ORDER BY t1.ActivityID) row

FROM @tbl2...

Try to google or search this site for the difference between COUNT(*) and COUNT(column_name).

It's worth to notice that laddu4700 already posted his question in a different thread.

Based on the code in the sproc you posted:

Why don't you add a (referential) foreign key...

since SQL Server doesn't know if you'd like insert the value into the ID or name column, you need to specify it:

insert into ID1(ID) values(8)

And no, SQL Server will not...

Why don't you use referential integrity together with cascaded delete?

Either you provide a value for each column or you need to identify the column(s) you'd like to insert the values.

There are numerous tools available to import data such as SSMS, BULK INSERT, OPENROWSET, linked server, SSIS, BCP.

Each one has its own strngth and weaknesses.

Question aside: is this a real-world...

One option to add the identity property would be using SSMS (right-click on the table -> design).

"Under the hood" it creates a new table, insert the original data, drop the...

In SS2K5 you could use a trigger and insert the the data of the internal tables INSERTED and DELETED into an audit table.

Your view would be based on the 10...

Here's a short example using LEFT JOIN:

DECLARE @tbl TABLE

(

ID INT IDENTITY (1,1),

ActivityControllerID INT,

ActivityID INT

)

INSERT INTO @tbl

VALUES(4,2),

(4,4),

(4,5),

(4,6),

(8,1),

(8,3),

(8,4),

(8,5),

(8,6),

(8,7)

DECLARE @tbl2 TABLE

(

ActivityControllerID INT,

...

You could check if your Activation Stored proc is running before processing the next message.

Try to eliminate the root cause (= the duration of the "long running query"). It might be an option to use the Divide'n'Conquer concept if this is a really complicated...