Inserting Data into a table, and checking for duplicates. 3

[paulmoss]

Hi

I am currently trying to insert some data from another table, but would like to exclude any rows that contain duplicate data.

Ok the story behind this to help you guys and girls to understand why I want to do this. Recently we ran a process on our accounts package and it removed some data from our database. Luckily we have a backup of the table.

I ran a standard insert statement and managed to get the information into a test copy of our database. However after doing this we noticed that there were duplicate records.

So now I am trying to come up with an SQL script that will compare 2 tables and only insert the rows that are not duplicates.

Is this possible?

Thanks in advance for your help

 

[ca8msm]

You can just build a select query to get all records from one table that are not in another e.g.

select t1.field1
from table1 t1
where not exists (select 1 from table2 t2 on t1.field1 = t2.field2)

(Note: you could also use a left join and check for a null value to do this)

Then, just add your insert before that select statement if you are sure it produces the correct results e.g.

insert into table1 (field1)
select t1.field1
from table1 t1
where not exists (select 1 from table2 t2 on t1.field1 = t2.field2)

If you didn't want duplicates though, you should have added a unique constraint!



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[paulmoss]

Thanks for your prompt reply. I apologise I didn't mention this in my initial post. But the recovered table is in another database. What would I need to change in the insert statement if the table was in another database?

 

[ca8msm]

Assuming you have access to the database, you can use the format [database].[owner].

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[paulmoss]

Ok so should my select statement look something like this

[Code/]
SELECT customerid,customername
FROM database1.owner.table1
WHERE not exists (select customerid, customername from database2.owner.table1 on database1.owner.table1.customerid = database2.owner.table1.customerid
[/code]

 

[tran008]

select a.customerid, a.customername
FROM
(
SELECT a.customerid, a.customername
FROM database1.owner.table1 A
WHERE NOT EXISTS
(SELECT 1 FROM database2.owner.table1 B
where a.customerid=b.customerid
a.customername=b.customername)
)tbl1

 

[paulmoss]

What does the a. and the b. represent?

 

[AccountingTechie]

Hi all - along these same lines, there is a way to check for existing duplicates in a database using one of the fields (say street address, which of course is not going to be unique) using the word "having". How would you write that select query? Thanks

 

[gmmastros]

Paul,

The A and B are simple aliases. Sometimes aliases are required, like when you use a derived table, and sometimes they are not required. Often times using an alias makes the code more readable, and sometimes it doesn't. When using aliases, I prefer to use the AS keyword, but again, it's not necessary. So, looking at tran008's code, with a little formatting and the AS keywords...

select [!]tbl1[/!].customerid, [!]tbl1[/!].customername
FROM   (
       SELECT  a.customerid, a.customername
       FROM    database1.owner.table1 [!]As[/!] A
       WHERE   NOT EXISTS (
          SELECT 1 FROM database2.owner.table1 [!]As[/!] B
          where  a.customerid=b.customerid
          a.customername=b.customername)
       ) [!]AS[/!] tbl1

Honestly, I haven't been following this thread so I won't comment on whether this code would solve your problem.

I do notice a problem with it, though. When you use aliases, you need to be consistent with them. The A and B aliases are used in the derived table and are therefore not accessible to the outer query. As such, the select clause needs to use the tbl1 alias (as I show in the above query).

Make sense?

AccountingTechie,
I encourage you to start a new thread for your question. We are willing to help you, but each un-related question should have it's own thread.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[paulmoss]

Would I need to create a join for each field in the table or just the one that I want to do the comparison on?

I apologise for asking so many questions, but when it comes to coding like this I am a complete newbie

 

[ca8msm]

You only join to the field that is common between the two tables.

The easiest way to find out the answers to your question is to simply test all of this yourself first by just writing the select without the insert (like I showed in my first post) and simply viewing the results to make sure they are correct before adding the insert.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[paulmoss]

I have now been informed that this is no longer required. So I thank you for your help. It has been really informative, even though I haven't been able to get any of the scripts that I've tried to work. I put that down to my own stupidity