Here is some code that does the job, not perfect though and leaves some room for improvements.

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* Staging tables */

create table dbo.tmp_management

(memberid int,

pid int,

firstname varchar(10),

lastname varchar(10),

DOB...

SQL_Surfer (8/16/2014)

---

I've a CSV file as source going to OLEDB Destination (SQL table)

Row delimiter is CRLF

Text Qualifier is "

Issue is some data value inside itself contains " to represent...

halifaxdal (8/16/2014)

---

Thanks for the discussion but I am sorry I didn't see that your reply is talking my question here:

What I am looking for is a simple query (with no...

In the old days (pre 1998) this was simple, only handful of top level domains. Today this is quite complicated and the only way is to maintain a registry of...

Koen Verbeeck (8/16/2014)

---

Luis Cazares (8/14/2014)

---

Grant Fritchey (8/14/2014)

---

Evil Kraig F (8/14/2014)

---

djj (8/14/2014)

---

Wish I could remember what last year was like, I remember some nice days for '77 as...

Evil Kraig F (8/15/2014)

---

Brandie Tarvin (8/15/2014)

---

It's Wednesday. 3 people walk up to your cube. One is from the QA team, one is from the reporting team, and one is a...

I agree, anything one can...

Ed Wagner (8/15/2014)

---

Jeff Moden (8/15/2014)

---

GilaMonster (8/15/2014)

---

Eirikur Eiriksson (8/15/2014)

---

Koen Verbeeck (8/15/2014)

---

GilaMonster (8/15/2014)

---

However OP said

I really don't like this temp table...

Ed Wagner (8/11/2014)

---

GilaMonster (8/11/2014)

---

paul.knibbs (8/11/2014)

---

Jeff Moden (8/9/2014)

With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia,...

Koen Verbeeck (8/15/2014)

---

GilaMonster (8/15/2014)

---

However OP said

So, no row number, no temp tables....

serviceaellis (8/15/2014)

---

JeeTee (8/15/2014)

---

If MemberTypeID 2 is all you care about, you can simply omit the lines of code from Eirikurs example that reference MemberTypeIDs you don't care about.

Is this for...

You can try out these methods as previously suggested, hard to understand why going through all the trouble instead of using ROW_NUMBER!

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @RESSET TABLE

(

...

Look into the MAXIMUM DEGREE OF PARALLELISM and COST THRESHOLD FOR PARALLELISM[/url] settings.

😎

My bad, sorry for the incomplete answer;-)

The complexity of not using an aggregate is...

It is of course possible on almost static databases with mostly reads and hardly any inserts/updates, but in general it is unlikely. There may be other reasons where one would...