Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-21-2006, 06:56 AM   #1
rsbutterfly16
Registered User
 
Join Date: Jun 2006
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
rsbutterfly16 is on a distinguished road
DTS omit duplicate rows.

Hi guys , I am trying to do a dts in which copies a table with duplicates, and then puts the data but it cannot have any duplicates. What is the best way to do this? I remember long time ago I saw a trick of how to do this by coping the table and then inserting a pk into the table, not sure of how it was, but I donít seem to find the article Ö I donít want to omit all the duplicates, I have to keep one row and then omit the other copies.

rsbutterfly16 is offline   Reply With Quote
Old 12-21-2006, 07:27 AM   #2
KeithG
AWF VIP
 
KeithG's Avatar
 
Join Date: Mar 2006
Location: Illinois
Posts: 2,592
Thanks: 0
Thanked 4 Times in 4 Posts
KeithG will become famous soon enough KeithG will become famous soon enough
Put Distinct after Select in your SQL statement
KeithG is offline   Reply With Quote
Old 12-21-2006, 07:36 AM   #3
rsbutterfly16
Registered User
 
Join Date: Jun 2006
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
rsbutterfly16 is on a distinguished road
thank you Keith, but that is the first thing i tried, in the source tab in DTS wizard where i have the select statememt i put Select Distinct .....

but i stilll keep gettting the error the task reported failure on execution the statement has been terminated cannot insert duplicate key row .....

rsbutterfly16 is offline   Reply With Quote
Old 12-21-2006, 07:41 AM   #4
KeithG
AWF VIP
 
KeithG's Avatar
 
Join Date: Mar 2006
Location: Illinois
Posts: 2,592
Thanks: 0
Thanked 4 Times in 4 Posts
KeithG will become famous soon enough KeithG will become famous soon enough
It sounds like you have a duplicate value in your Primary key not a duplicate record.
KeithG is offline   Reply With Quote
Old 12-21-2006, 07:45 AM   #5
rsbutterfly16
Registered User
 
Join Date: Jun 2006
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
rsbutterfly16 is on a distinguished road
is there any way to skip the ones that have duplicate values int the PK?
rsbutterfly16 is offline   Reply With Quote
Old 12-21-2006, 08:18 AM   #6
rsbutterfly16
Registered User
 
Join Date: Jun 2006
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
rsbutterfly16 is on a distinguished road
hi Keith so this is what i did and it seems to have worked, i schedule dto do this once every month.

this DTS is very basic just copies a table(it has duplicates) from another database and then put the data into my table with different column names.
In my table i inserted a pk autonumber identity column and check on the ignore duplicate key. then run the dts again, it does error out saying that ignore duplicate keys were ignored. and all the rows got inserted into my table :-).

Did i do this correctly? i tested and all the rows are there, just skipped the ones that had duplicate records only inserted one of each which is what i wanted.

rsbutterfly16 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Without Duplicate Rows? terryzs Queries 3 10-26-2006 12:57 AM
[SOLVED] How do I omit duplicate addresses? ismilelots Reports 0 01-30-2006 03:07 PM
duplicate records (rows) across fields DK gurung Queries 11 08-29-2003 01:53 PM
Duplicate Rows k209310 General 6 01-22-2003 03:55 AM
Report based on a query has duplicate rows clark Forms 1 03-14-2002 08:46 AM




All times are GMT -8. The time now is 09:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World