Solved

Get rid of Duplicate records in MS SQL 2000

Posted on 2006-11-29
2
430 Views
Last Modified: 2011-09-20
Table R
Category   Classes  Brand      Model      
AAA          BBB       CCC        DDD
BBB          CCC       DDD        EEE
CCC         AAA       BBB         ZZZ
CCC         AAA       BBB         EEE

Table A
Category   Classes  Brand      Model      
AAA          BBB       CCC        AAA
BBB          CCC       DDD        FFF
CCC         AAA       BBB         ZZZ (* - already exist in table R)
CCC         AAA       CCC        ZZZ

I need to insert data in Table A into Table B, but don't want the duplicate record (see *) as Table R is a reference table and no duplicate records in allowed.  How to prevent Table R from having duplicate records after the insertion?

Please advice.

Thank you.
0
Comment
Question by:woo_kh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 50 total points
ID: 18043360
INSERT INTO R
SELECT *
FROM A a1
WHERE NOT EXISTS (SELECT 1 FROM R WHERE Category = A1.Category and Classes = A1.Classes and Brand = A1.Brand AND Model = A1.Model)
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18043373
insert into tableA
select * from tableB B
left join tableR R on R.Category=B.Category and R.Brand=B.Brand and R.Model=B.Model
where R.Category is null
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question