Don't want to insert duplicate records 2

[roblasch]

I have scoured the web looking for answers to this problem. i am suprised that it is so difficult to solve, and am hoping that I am missing something. I have a file of events that gets sent to me each month. It contains six months worth of listings. Most of the entries are already in the database. I need to insert the records that ARE NOT ALREADY THERE. I need to check that every column is the same! Sometimes many of the fields are the same(there may only be one or two that are different and they are not the same every time). I thought that this would work, but it returns all the rows.

SELECT StartDate,EndDate, [Town], Event, Description, Location, Time,
Cost, Phone, PhoneAlt, 1 as active
FROM tblInputEvents

where exists(select StartDate,EndDate, [Town], Event, Description, Location, Time,
Cost, Phone, PhoneAlt, 1 from tblEvents )

This seems like it would be a common problem with a simple solution, but I am not finding it. Any insight would be greatly appreciated.

 

[branko]

Hi,

My SQL is getting a bit rusty lately and I don't have the possiblity to test it now, but this is a suggestion:

SELECT *
FROM Table2 LEFT JOIN Table1 ON Table2.FK = Table1.id
WHERE Table1.id Is Null

Add all the fields you want to check in the join.

Hope this helps you,
Branko

 

[MalcolmW]

There are three set operators - UNION, INTERSECT, and EXCEPT. Union is by far the most common, but the others have uses too.

SELECT StartDate,EndDate, [Town], Event, Description, Location, Time,
Cost, Phone, PhoneAlt, 1 as active
FROM tblInputEvents
EXCEPT
SELECT StartDate,EndDate, [Town], Event, Description, Location, Time,
Cost, Phone, PhoneAlt, 1
FROM tblEvents
Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[bperry]

malcolm:
I don't think that will work. Unless I am mistaken (often am), in MS SQL Server we only have the Union set operator in T-Sql.

We do have the Union(), Except(), and Intersect() functions, but they are only used in Analysis services.

bperry

 

[roblasch]

Malcolm's solution looks like it has potential. But I don't understand where the 'Where table1.id=null' comes from.

 

[genomon]

How about INSERT INTO your_table (fld1, fld2, fldn)
SELECT DISTINCT a.fld1, a.fld2, a.fldn
FROM input_table a
WHERE (a.fld1 NOT IN (SELECT fld1 FROM your_table))
AND etc.
Plenty verbose, probably don't need the DISTINCT clause if input is unique. Must be a better way, just a passing thought.....

 

[bperry]

>>Malcolm's solution looks like it has
>>potential. But I don't understand where
>>the 'Where table1.id=null' comes from.

roblasch:
I think you may have meant 'branko's solution'.

re: where the 'Where table1.id=null' comes from.
branko is left joining Table2 to Table1 to look for matches, and the WHERE restriction means "only select records where no match exists in Table1 for a Table2 value"

bp

 

[bperry]

You could try this. (But I hope there's a better way.)

INSERT Into tblEvents
SELECT StartDate,EndDate, [Town], Event, Description, Location, Time, Cost, Phone, PhoneAlt, 1 as active
FROM tblInputEvents ti

WHERE NOT EXISTS
(select * from tblEvents te where
te.startdate = ti.startdate and
te.EndDate = ti.enddate and
te.Town = ti.Town and
te.Event = ti.Event and
te.Description = ti.Description and
te.Locatino = ti.Location and
te.Time = ti.Time and
te.Cost = ti.Cost and
te.Phone = ti.Phone and
te.PhoneAlt = ti.PhoneAlt
)


I wasn't sure what you wanted to do with 'Active' so I left it out.
bp

 

[roblasch]

I finally get it. You don't have to hit ME over the head with a 2 x 4. Thanks to all who offered input, even Malcolm and especially BP and Branko