02 Jan 09 MSSQL Large Sub Query Impossibility
I recently had to query to find out how many records from one table were not in another really large table. When you do a select statement like this in MSSQL it overflows and gives you a response of 0. Not very helpful:
select count(*) from Table1 where Field Not In (Select Field from Table2)
All other ideas failing, I finally resorted to writing a query using a temp table variable:
set nocount on;
declare @Tables table (
PK int IDENTITY(1,1),
Field1 varchar(100), Field2 varchar(100)
);
insert into @Tables(Field1 , Field2 )
select t1.Field, t2.Field from Table1 t1
left outer join Table2 t2 on
t1.Field = t2.Field;
set nocount off;
select count(*) as Count, Field1 from @Tables
where Field2 is Null
Group by Field1
Having Count(*) < 2;
Talk about complicated! If you have any other better ways of doing this – please let me know!
Learn more about your human rights at HumanRights.com
Leave a Comment
