try:

INSERT INTO DestinationTable(UserId,DriveCode,Intrested,DriveId)

SELECT p.userid,p.DriveCode,p.0,p.DriveID

FROM @paramtertable p

LEFT JOIN DestinationTable d

ON d.UserId = p.UserId

AND d.DriveCode = p.DriveCode

WHERE d.UserId IS NULL

GilaMonster (6/21/2010)

---

...

There's no OR here. If you want to retrieve the data in a particular order, there must be an order by on that select. The presence of a clustered...

As RAISERROR WITH LOG will write into EventLog it will negatively affect performance of the existing code for sure.

BUT!

The real question is: Will the negative effect of its use...

...

I spoke to a couple of the people after the session. They'd either been told by colleagues or had encountered blocking in their queries, went looking for a way to...

GilaMonster (6/19/2010)

---

...

I asked in a TechEd presentation last year how many people used nolock often (session on query hints and plan guides). About 1/4 of the room put up their...

Jeff, in a final code I would use temp table (like in the first sample). But in this case I did it with CTE to save few lines of code...

I am not sure what you mean by dynamic sql in your case.

Dynamic sql is the way to execute sql from a given string. Achieved by using EXECUTE(@SQL) function or...

Jeff Moden (6/18/2010)

---

Eugene Elutin (6/18/2010)

---

...

Nicely done but I believe you're dropping the first hour. Also, be careful of the 23:59:59.000 thing. A lot of data can happen in...

Lynn Pettis (6/19/2010)

---

Eugene Elutin (6/18/2010)

---

Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.

I can be wrong,...

Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.

I can be wrong, of cause.

Nice problem.

Here is my first attempt:

declare @periodStart datetime

declare @periodEnd datetime

-- they must be inside of the same day!!!!

set @periodStart = '20100601'

set @periodEnd = '2010-06-10 23:59:59.000'

declare @givendate datetime

set @givendate =...

I am assuming this query is run for reporting purposes, therefore dirty reads might be acceptable.

Without table DDL and data sample setup it is hard to test

, but try:

select

count(distinct pmi.Id) as TotalPrograms

,count(pt.Id) ...

try:

select id

from mytable

group by id

having min(val) != max(val)

It's simple! You are missing the setup of tables and sample data population scripts to represent your question/case in a helpfull way, which would...