inserting distinct records - 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)

inserting distinct records

Author

Topic

drman
Starting Member

38 Posts

Posted - 2011-03-31 : 15:25:29

I am struggling on this one, it is probably very simple, but I dont have a clue. I am not even sure if I can describe it correctly.

Table A:

Name GroupID
____ _______
Bob 1
Jim 1
Frank 1
Bob 2
Bill 2

Table B:

Name GroupID
____ _______
Mark 1
Dan 1
Mark 1
Dan 2
Bill 2

I am looking to insert distinct records (identified by 2 fields) from Table B into Table A, but cannot have duplicate records in Table A.

First I tried to determine unique values in Table B by using a statement similar to: (not sure if this is correct, but works)

select distinct(name + '-' + cast(groupID as char), name, groupID from TableB and the result is...

Mark - 1, Mark, 1
Dan - 1, Dan, 1
Dan - 2, Dan, 2
Bill - 2, Bill, 2

I now want to insert the names with the corresponding groupIDs into TABLE A without duplicating records.
Bill - Group 2 is already in TABLE A so he would not be inserted, only the other 3

I don't know where to start with the Insert command... Everything I tried has gotten me nowhere closer to a solution.

Please point me in the right direction.

Thanks

Yak

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 18:42:14

Instead of first finding which records don't exist in table A and then trying to insert them from Table B into Table A, think of it in the opposite way. Sort of like, "Hey SQL, insert these rows into table A for me. By the way, insert it only if it does not exist". In code that would be


insert into TableA -- insert these rows into Table A
select distinct *
from TableB tb
where not exists -- but only if it does not exist in that table
(
   select * from TableA ta where ta.Name = tb.Name and ta.GroupId = tb.GroupId
)

Even though I *think* about it that way, SQL is really doing it the way you are thinking about it.

Edit: Add a distinct to the select query to avoid duplicates from TableB.

drman
Starting Member

38 Posts

Posted - 2011-04-01 : 11:48:55

Thank you VERY much. It was a little more involved then your explaination, however, you definitely pointed me in the correct direction.

Thanks again..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 16:06:12

YVW. Glad it helped you even if not all the way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources