How to insert into Temp Table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

Maybe something like?

insert into #TempResult
select distinct
Memberid,
Month,
Year
from Rebate a
where not exists
( select 0 from #TempResult b
where a.memberid = b.memberid
and a.month = b.month
and a.year = b.year
)

Dave

|||

If you are using SQL Server 2005, you can also use the EXCEPT operator:

insert into #TempResult
select Memberid,
Month,
Year
from Rebate

except
select Memberid,
Month,
Year
from #TempResult

|||Mugambo.. thanks. but why i have to use "select 0 "instead of "select * "?|||

Lax:

You do not; I choose zero because the column selected in this case doesn't matter; what matters is whether or not the row exists. This is a semi-join? Can somebody confirm the semi-join?

Dave

|||( Maybe a left anti semi join )