insert, using inner join - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

insert, using inner join

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-01-05 : 05:47:13

i have this code :


insert into t2(col1)
select distinct t1.DESCRIPTION
from	TBL1 t1 (nolock) left outer join TBL2 t2 (nolock)
	on	t1.DESCRIPTION	= t2.DESCRIPTION
where t2.DESCRIPTION is null

what i wantto do, is to find all the values in TBL1, which are not
in TBL2,make a distinct on the result and insert it into TBL2
the problem is when i do it i get that i try to insert a duplicate value into t2, while in the select i have already removed all the the values that exist, and left only with the new ones.
any idea?
thanks
Peleg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 05:50:27

As per your query it takes only distinct set of description value from t1 which are not in t2. Can i ask if this is your full query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 05:51:57

Also is your db using a case sensitive collation? Also make sure your description values dont have any additional characters like space,carriage return, etc.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-05 : 05:59:52

also insert into t2 is not possible unless t2 is a real table and not the alias from the select!

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-01-05 : 06:28:12

the insert is on the real table name not on T2
and there are no spaces or anything else

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 03:29:44