Solved

Get rid of Duplicate records in MS SQL 2000

Posted on 2006-11-29
2
433 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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

635 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