September 8, 2006 at 11:59 am
Ok I've done this before but I must have forgotten the correct syntax so I created an example script that is trying to do the same thing.
declare @tmpActiveProfiles table(BRP_Flag char(1), uid int)
insert into @tmpActiveProfiles(BRP_Flag , uid)
select BP.BRP_Flag, BP.UID from
(
select 'N' as BRP_Flag, 1 as UID
union
select 'N' as BRP_Flag, 2 as UID
union
select 'N' as BRP_Flag, 3 as UID
union
select 'Y' as BRP_Flag, 3 as UID) BP
left join @tmpActiveProfiles T on T.UID = BP.UID
where T.UID is null
select * from @tmpActiveProfiles
----- OUTPUT -----
BRP_Flag uid
-------- -----------
N 1
N 2
N 3
Y 3
Why did the last record get inserted? I thought the left join would not be null on the last record so it should not have been inserted?
I've also wrote the same query with exists and it's the same results.
Any ideas.
September 8, 2006 at 12:33 pm
The last record will be inserted because the temp table is empty at the time the query is executed. If you execute each statement individually by left joining it to the variable table, then the last record would not be inserted.
insert into @tmpActiveProfiles(BRP_Flag, uid)
SELECT 'N' as BRP_Flag, 1 as UID LEFT JOIN
@tmpActiveProfiles t on t.UID=bp.UID WHERE t.UID is null
insert into @tmpActiveProfiles(BRP_Flag, uid)
SELECT 'N' as BRP_Flag, 2 as UID LEFT JOIN
@tmpActiveProfiles t on t.UID=bp.UID WHERE t.UID is null
...
Brian
September 8, 2006 at 12:43 pm
Ok that makes sense, now that I think about it when I did it before the table was already populated so duplicates were ommitted, i've never had the insert list with dupes.
In that case if I had a result set comming back as in my example above how would I select records with the unique UID, it doesn't matter which of the last two records I get just that I only get one of them.
Select top 3 won't work because my real result set is much larger.
September 8, 2006 at 12:54 pm
Thankyou bdohman!
Ok I figured it out, maybe not the best way but this is what I came up with. Doing two inserts
with a where clause.
declare @tmpActiveProfiles table(BRP_Flag char(1), uid int)
insert into @tmpActiveProfiles(BRP_Flag , uid)
select BP.BRP_Flag, BP.UID from
(
select 'N' as BRP_Flag, 1 as UID
union
select 'N' as BRP_Flag, 2 as UID
union
select 'N' as BRP_Flag, 3 as UID
union
select 'Y' as BRP_Flag, 3 as UID) BP
where BP.BRP_Flag = 'Y'
insert into @tmpActiveProfiles(BRP_Flag , uid)
select BP.BRP_Flag, BP.UID from
(
select 'N' as BRP_Flag, 1 as UID
union
select 'N' as BRP_Flag, 2 as UID
union
select 'N' as BRP_Flag, 3 as UID
union
select 'Y' as BRP_Flag, 3 as UID) BP
left join @tmpActiveProfiles T on T.UID = BP.UID
where T.BRP_Flag is null and BP.BRP_Flag = 'N'
select * from @tmpActiveProfiles
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply