quote:

---

Originally posted by sqlbug

I am tranferring some data from an old database to a new one.
The table structure is little different. So, I can't just move the whole data to the new one.
When doing this - I found that the old one has some duplicate entries not liked by my new key. Here's the query:
INSERT INTO ABC_LOG (ABC_NUM,ABC_TYPE,ABC_STATION,ABC_PARAM,ABC_START_DATE,ABC_START_TIME,ABC_METHOD,ABC_PARAM_LEVEL,ABC_PARAM_UNIT,ABC_GUIDE_LEVEL,ABC_GUIDE_UNIT,.............)
SELECT XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME,XYZ_METHOD,XYZ_PARAM_LEVEL,
XYZ_PARAM_UNITS,............. FROM XYZ_LOG,STN_INFO
WHERE XYZ_SITE_ID = STN_PLOTSITE_ID AND XYZ_PLOT_ID = STN_PLOT_ID

Among the fields in ABC_LOG table, first 6 are primary key fields.
If I use DISTINCT instead of just select, I get less duplication - but I still get some and using DISTINCT on all these columns is not correct because all of them are not the key fields or unique constrained.

Any idea how I can do it? I was thinking like
1) if it is possible to select each row, then iterate and see if there is any duplication based on those 6 fields - if not, insert.
2) I transferred the data to a temp table. So, is it possible to just DELETE the duplicate entries from this table and then do the insert? How can I delete duplicate entries from a table?

Thanks.

---

quote:

---

Originally posted by sqlbug

Yes - I am using SQL 2005. But can you please explain how I do this
"ROW_NUMBER() over(partition by pk.columns)"?

---