I'm a newbie. Relatively comfortable with Access/jet but learning SQL Server.

How do I handle importing or transferring rows with duplicates in the primary key columns?

In an Access Project a simple Append stored procedure just stops when it gets to the first row containing duplicates, as you know. No rows are transferred to my target table. Same result when using SQL Query Analyzer and INSERT INTO target SELECT * FROM source. Just stops. Both give error message about dups in primary key (I think).

I've also tried an INSTEAD OF trigger on my target table. After various tests with this I've inferred that the INSTEAD OF trigger fires only one time and not for each incoming row, as I would want. (BTW, Books Online for INSTEAD OF suggest it should work in handling key violations, but BO is unclear if that means a batch of rows or just one.)

I've had qualified success with DTS and adjusting some properties in the Transfer Data Task Properties dialog. I specified an Exception file. I unchecked 7.0 Format, checked Error Text, Source error rows, and Dest error rows boxes. Set the Max error count value to cover the number of duplicates, and set the Insert batch size value to 1. This seems to work and does create the Exception file listing the row numbers that had primary key duplicates. BUT it doesn't create either the Exceptionfile.txt.source or Exceptionfile.txt.target files. I was hoping for a text file containing the rows not transferred.

My questions are:
1. How do I get the Exceptionfile.txt.source and Exceptionfile.txt.target files to be created?
2. Am I correct that an INSTEAD OF trigger is not the way to go?
3. Is there another, different, strategy that I should consider?

Thanks.

Jerry

In your transformation task portion select the tab 'option' and under exception file place or browse to the path of a previous named file. for example: 'Exception.txt'. any exception created will be store in this file with all needed information.

Jaime E. Maccou
Applications Analyst

Thanks for the input, Jaime. I've included below the basics from the Exception.txt file.

-----------------
DATA TRANSFORMATION SERVICES: Data Pump Exception Log


Package Name: AppendFrom APPFIXEDToAPPFIXEDtbl
Package Description: DTS package description
Package ID: {1117D2F3-1754-4245-934C-2C9D60F93057}
Package Version: {E1F2EFE6-6157-4CF7-891F-5E7CA34DE4BA}
Step Name: Copy Data from APPFIXED to [Jerrys Second SQL Database].[dbo].[APPFIXED] Step

@@ExecutionStarted

Execution Started: 11/15/2004 9:44:58 AM

@@LogSourceRows: C:\Documents and Settings\My Documents\Junk\APPFIXEDImportExceptions.txt.Source
@@LogDestRows: C:\Documents and Settings\My Documents\Junk\APPFIXEDImportExceptions.txt.Dest

@@ErrorRow: 1
Error at Destination for Row number 1. Errors encountered so far in this task: 1.

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:The statement has been terminated.
Error Help File:
Error Help Context ID:0


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Violation of PRIMARY KEY constraint 'PK_APPFIXED2'. Cannot insert duplicate key in object 'APPFIXED'.
Error Help File:
Error Help Context ID:0
@@SourceRow: Not Available
@@DestRow: Not Available
-------------------

As you can see, the only information provided about the exception rows, other than the row number, is : @@SourceRow: Not Available. Same for DestRow.

This is contrary to Chaffin, Knight and Robinson in (Wrox Press) Professional SQL Server 2000 DTS, page 184 (bottom).
I was hoping to see the actual exception rows.

I'm also hoping I can get some help with my basic questions:
1. How do I get the Exceptionfile.txt.source and Exceptionfile.txt.dest files to be created?
2. Am I correct that an INSTEAD OF trigger is not the way to go?
3. Is there another, different, strategy that I should consider?

Thanks, everybody.

(BTW, in Enterprise Manager, when I go to menu Help and click on About Microsoft Management Console, I see the following:
"Microsoft Management Console 2.0
Version 5.1 (Build 2600.xpsp_sp2_rtm.040803-2158: Service Pack 2)
Copyright 1981-2001 Microsoft Corporation."
Could this be a patch issue??)

Thanks again.

Jerry