My post got lost between some sol NOT SET based so I ll posted again (the while was just to generate the numbers table)

SET BASED solution

declare @v-2 table(val varchar(50))

insert...

when maxID=minId and a.UserId=b.UserId then 1

--if there is no MAX(all are equal) than check to see

if UserID is equal  (ID group is catched in join!)

when maxID>minID and maxID=b.CustomerID then 1

--if there...

nice and && : ))

I looked like 5 min to see the diff ....: )

With a small add of ',' at the end of each string you can do

declare @v-2 table(val char(50))

insert into @v-2

SELECT 'dog, mouse,cat,' UNION ALL

SELECT 'mouse,...

This querry treats the cases where (all CustomerId are equal) or the MAX row is unique (I don't have 2 rows with MAX and some other with smaller values)

Select b.*

from...

CREATE CLUSTERED INDEX [IndexTMP] ON [tempdb].[#tmpTBL]() WITH FILLFACTOR = 70 ON [PRIMARY]

I hope your tables have INDEX on FIELD

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT NULL,

X1 INT NULL,

X2 INT NULL,

X3 INT NULL,

X4 INT NULL,

visitnumber INT NULL

)

INSERT @F

SELECT 1, 12, 1,...

Because you have so many rows this should work better

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT NULL,

X1 INT NULL,

X2 INT NULL,

X3 INT NULL,

X4 INT NULL,

visitnumber INT NULL

)

INSERT @F

SELECT 1, 12, 1,...

You need an INDEX on field on that @Tmp table which has to be moved from var to temp table.

You need to do this more than once?

Srry Lunch Break

This is hard to do without a cursor ... so I transalted into 4 updates

HOPE you need to do this only once

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT...

MAYBE I didn't get the specs correctly ...

but can you describe why you should get

1 12 1 2 9 null   ??? 

X3 IS NULL in dest rec

from Source rec...

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT NULL,

X1 INT NULL,

X2 INT NULL,

X3 INT NULL,

X4 INT NULL,

visitnumber INT NULL

)

INSERT @F

SELECT 1, 12, 1, 2, null,null UNION ALL

SELECT 2, 1, 2, null, null,0...

with 1 to many relationship

according to this you might have :

tblx

key x1 x2 x3 x4

1    1   null null null

and tbly

Fkey y1 y2 y3 y4

1    ...

(MyField IS NOT NULL) AND (RTRIM(MyField)<>'')

LEFT OUTER JOIN

 will make that join possible and will return those rows that are in first table and not in second table  [Baseball65K50].[Col007] IS NULL