Preventing Duplicate entries in a database

[SirGally]

Hi there,

I almost have my DTS complete. It is scheduled too transfer the past 24hrs entries from one database to another. The only trouble i am having is preventing duplicate entries being transfered.

Can I prevent an entry from being copied from the source DB to the destination DB if say 3 of the fields in that entry match an entry already made to the destination DB?

eg Date, MachID, Type ??

Any input welcome!!

SirGally

 

[AlexCuse]

Sure. Set up a staging table to make initial import to (not necessary if starting with a data source you can query).

Then rather than using table/file as source, it can be a query.

select a.col1, a.col2, a.col3,a.col999
from StagingTable a
left join DestinationTable b
on a.Date = b.Date
and a.MachID = b.MachID
and a.Type = b.Type
where b.[IDCol] is null

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SirGally]

Thanks, i think we are getting closer. This is what I have:

SELECT Meters.mach_id, Meters.poll_type, Meters.bus_day
FROM Meters

LEFT OUTER JOINSingleMeters ON
Meters.bus_day = SingleMeters.bus_day
AND Meters.mach_id = SingleMeters.mach_id
AND Meters.poll_type = SingleMeters.poll_type

WHERE (SingleMeters.bus_day IS NULL)

ORDER BY Meters.bus_day


I am still getting ALL entries including duplicates, I think im losing it on the where statement??!!

Thanks

 

[AlexCuse]

And you are trying to place data from Meters into SingleMeters, correct?

Can you post sample data for the two tables, and what you would want the result of your seelect to be? That looks like it should work, but there may be something about your data that necessitates a change.

Thanks,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[SirGally]

Mach_iD Poll_ype Bus_day
3 6 28/12/2006 06:30:01
4 6 28/12/2006 06:30:01
5 6 28/12/2006 06:30:01
2 6 28/12/2006 06:30:01
2 6 28/12/2006 06:30:01
3 6 28/12/2006 06:30:01

This is Meters

The SingleMeters table is emtly with the same three Cols
Thanks

 

[SirGally]

Mach_iD Poll_ype Bus_day
3 6 28/12/2006 06:30:01
4 6 28/12/2006 06:30:01
5 6 28/12/2006 06:30:01
2 6 28/12/2006 06:30:01

Thsi is what should appear in the singlemeter table!

Thanks

 

[AlexCuse]

Ah that is the problem. I thought you were trying to prevent adding duplicates already in singlemeters. Try this:

select distinct Mach_ID, Poll_Type, Bus_Day
from Meters

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SirGally]

Top man Alex,

that worked.

Thanks very Much!

SirGally