Insert into Table B records that in table a not in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert into Table B records that in table a not in

Author

Topic

nnogueira
Starting Member

18 Posts

Posted - 2009-09-25 : 18:17:36

Hello Guys.

I have two tables with same fields, and actually i uses NOT IN Statement, but it not too fast with big tables and some time out problems has occured.

My actual statement is:

select NumProd,DescProd
From Table A
WHERE (NumProd NOT IN (Select NumProd From table B AS EXPR1)

And i tried too

SELECT t1.NumProd,t1.DescProd
FROM TableA AS t1 LEFT OUTER JOIN
TableB AS t2 ON t2.NumProd = t1.NumProd
WHERE (t2.NumProd IS NULL)
GROUP BY t1.NumProd

And it cause timeout too..

Well, gurus of SQL, someome can help-me ?

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-26 : 06:21:12

I go always to replace my old "not in (...)" by "where not exists" and it is much faster.

select NumProd,DescProd
From Table A
WHERE not exists (select * from Table B where B.NumProd = A.NumProd)

If this not helping then think about proper indexes.

No, you're never too old to Yak'n'Roll if you're too young to die.

nnogueira
Starting Member

18 Posts

Posted - 2009-09-28 : 11:12:22

I've searched for solutions for this case, and i see something strange.

Records in table A repeats sometimes, ex:

NumProd,DescProd

001,PEN
002,Tape
003,Cigar
001,PEN

I tried run

select NumProd,DescProd
From Table A
WHERE (NumProd NOT IN (Select NumProd From table B AS EXPR1)

---------------------OR

SELECT t1.NumProd,t1.DescProd
FROM TableA AS t1 LEFT OUTER JOIN
TableB AS t2 ON t2.NumProd = t1.NumProd
WHERE (t2.NumProd IS NULL)
GROUP BY t1.NumProd

----------------------OR

select NumProd,DescProd
From Table A
WHERE not exists (select * from Table B where B.NumProd = A.NumProd)

----------------------

And it take 42 seconds to finish in first run (when table B is empty), and run again, but so fast when table B has data (0.00001 secs).

Someome already see something similar ?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources