A 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)

A join...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-24 : 07:37:42

I want to compare all records in Table A with Table B and take those duplicates and place them in a new table called Table C. How would I write this stored procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 07:46:54

INSERT INTO TableC (fields...)
SELECT fields...
FROM TableA
WHERE PK IN (SELECT PK FROM TableB)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-24 : 11:11:50

Thanks!

cognos79
Posting Yak Master

241 Posts

Posted - 2008-09-24 : 11:39:51

You can also use "INTERSECT" operator to find matching records.
http://www.databasejournal.com/features/mssql/article.php/3605246

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-24 : 12:13:21

One thing I failed to mention I want the duplicate records but have only once occurance showing up in the new table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 13:16:50

quote:
Originally posted by JJ297

One thing I failed to mention I want the duplicate records but have only once occurance showing up in the new table.

didnt get that. you mean records having only single occurance each in table A & table B?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-24 : 13:59:35

I have two tables Pending and Cleared. If the same record is in both tables it should go to the Pending Table I want only the one record to show up in the pending table.

Here's an example:

Table A
FName LName Email
Brenda Smith Bsmith@abc.com
Karen Jones KJones@abc.com
Stacy Williams SW@abc.com

Table B

FName LName Email
Stacy Williams SW@abc.com
Beth Jones Beth@abc.com
Karen Jones KJones@abc.com

Pending Table

FName LName Email
Karen Jones KJones@abc.com
Stacy Williams SW@abc.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 14:04:10

quote:
Originally posted by JJ297

I have two tables Pending and Cleared. If the same record is in both tables it should go to the Pending Table I want only the one record to show up in the pending table.

Here's an example:

Table A
FName LName Email
Brenda Smith Bsmith@abc.com
Karen Jones KJones@abc.com
Stacy Williams SW@abc.com

Table B

FName LName Email
Stacy Williams SW@abc.com
Beth Jones Beth@abc.com
Karen Jones KJones@abc.com

Pending Table

FName LName Email
Karen Jones KJones@abc.com
Stacy Williams SW@abc.com

INSERT INTO Pending (FName,LName,Email)
SELECT a.FName,a.LName,a.Email
FROM TableA a
INNER JOIN TableB b
ON a.FName=b.FName
AND a.LName=b.LName

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-09-24 : 14:32:31

Perfect Thanks!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 14:52:43

quote:
Originally posted by JJ297

Perfect Thanks!!!!

Welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-25 : 06:17:23

quote:

Originally posted by visakh16

INSERT INTO TableC (fields...)
SELECT fields...
FROM TableA
WHERE PK IN (SELECT PK FROM TableB)

Avoid IN as much as possible

declare @test1 table(i int)
insert into @test1 select 10 union all select 56

declare @test2 table(j int)
insert into @test2 select 10

select * from @test1 where i in (select i from @test2)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources