select the overlap of two tables into a new table

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

select the overlap of two tables into a new table

Author

Topic

ConnieXujuan
Starting Member

48 Posts

Posted - 2011-12-29 : 17:12:10

Hi -,

I have two tables here: t1 and t2. The two tables have some rows that are overlapped. If I want to select the overlapped part into a new table how should my query be like?

I could not use the below query as it would have duplicated rows and give me error message:

select*
into
NewTable
from
t1,
t2
where
t1.ID=t2.ID

so how can I do a query to achieve it?

Thanks a lot!

Connie

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-29 : 18:11:11

select distinct *
into newtable
from t1
instersect
select distinct *
from t2

This gives you where every column in t1 is equal to every column in t2

Jim

Everyday I learn something that somebody else already knew

ConnieXujuan
Starting Member

48 Posts

Posted - 2011-12-29 : 19:34:17

Thanks a lot Jim!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources