I am attempting to use DTS from an Access 2002 database
table to import data into a SQL Server 2000 table. PK
entry in SQL Server table is created by a trigger.

SELECT @maxc = (SELECT Max(TBL_ID) FROM SQL_ServerTable)
SELECT @newc = (SELECT TBL_ID FROM inserted)
IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
IF @newc <> 0 or @newc <> null SELECT @maxc = @newc
UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID =
@newc
GO

SQL_ServerTable.TBL_ID int is not an identity and has a
default of 0

When using Access 2002 and ODBC to Link to
SQL_ServerTable then use an append query which does not
include TBL_ID to append the rows the SQL Server trigger
fires and a new TBL_ID is generated for each row.
However, when using DTS and do not include the TBL_ID
column or include the TBL_ID column with null values I
receive an error Violation of PRIMARY KEY
constraint 'TS_PRIMARY'. Cannot insert duplicate key in
object 'SQL_ServerTable'.

Thanks for any suggestions.

Hi Terry,

Sorry, I'm not sure the real situation. Would you please post the exact
reproduce steps?

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

To replicate the issue:
Create on a SQL Server 2000 sp3 the following objects:
A new database or place these objects into an existing
database

/****** Object: Table dbo.SQL_ServerTable ******/
CREATE TABLE dbo.SQL_ServerTable (
TBL_ID int NOT NULL CONSTRAINT
DF_SQL_ServerTable_TBL_ID DEFAULT (0),
CL_CODE int NOT NULL ,
TITLE_1 varchar (34) NULL ,
TITLE_2 varchar (34) NULL ,
VOL_NO varchar (9) NULL ,
ts timestamp NULL
)
GO

/****** Object: Table dbo. SQL_ServerTable ******/
ALTER TABLE dbo. SQL_ServerTable WITH NOCHECK ADD
CONSTRAINT TSQL_PRIMARY PRIMARY KEY CLUSTERED
(
TBL_ID
)
GO

/****** Object: Trigger dbo.SQL_ServerTable_ITrig ******/
CREATE TRIGGER SQL_ServerTable_ITrig ON SQL_ServerTable
FOR INSERT AS
DECLARE @maxc int, @newc int
/** COUNTER-EMULATION CODE FOR FIELD 'TBL_ID' **/
SELECT @maxc = (SELECT Max(TBL_ID) FROM SQL_ServerTable)
SELECT @newc = (SELECT TBL_ID FROM inserted)
IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
IF @newc <> 0 or @newc <> null SELECT @maxc = @newc

UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID =
@newc
GO
Add a few records to SQLServerTable

Create an Access 2002 database AccXPdb1.mdb
Create in AccXPdb1.mdb a table AccessXP_Table
CL_CODE Number long integer NOT NULL
TITLE_1 Text (34) NULL
TITLE_2 Text (34) NULL
VOL_NO Text (9) NULL

Add a few rows to AccessXP_Table

Create an ODBC DSN to SQL Server 2000 database containing
SQL_ServerTable

In AccXPdb1.mdb
link to SQL_ServerTable
Create an Append query Source AccessXP_Table
Append to SQL_ServerTable

The records will be appended and the TBL_ID column will
increment as expected.

Use SQL Server Enterprise Manager
Tools, Data Transformation Services, Import Data., Data
Transformation Services Import/Export Wizard
Data Source = Microsoft Access
File name = <path>AccXPdb1.mdb
Destination = Microsoft OLDB Provider for SQL Server <<IF
Microsoft ODBC Driver for SQL Server is selected here the
records are inserted correctly. HOWEVER, it took 3
minutes to insert 10,500 records>>
Server = YOUR SQL Server
Database = YOUR database containing SQL_ServerTable
Use a Query to specify the data to transfer
select query builder
select all columns from AccessXP_Table
No sort Order
Select Destination as SQL_ServerTable
An error is returned as follows:
Violation of PRIMARY KEY constraint 'TS_PRIMARY'. Cannot
insert duplicate key in object 'SQL_ServerTable'.

Hi Terry,

Thank you for your update, I am still looking into this issue and hope to
update you today or tommorow. Thank you for you patience.

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

Hi Terry,

Sorry for the delay response. According to your reproduce steps, I have
reproduced this issue on my side and I would like to list the exact
reproduce steps that are some different from yours in order to make the
explaination more clearly. We don't have much to do with Access since the
issue is mainly related to SQL.

1) Create a table with 2 columns. The table will be destination table.

CREATE TABLE D_TBL
( ID INT NOT NULL DEFAULT (0) PRIMARY KEY,
NUM INT NOT NULL
)
GO

2) Create a trigger on D_TBL

CREATE TRIGGER D_TBL_ITrig ON D_TBL
FOR INSERT AS
DECLARE @maxc int, @newc int
/** COUNTER-EMULATION CODE FOR FIELD 'ID' **/
SELECT @maxc = (SELECT Max(ID) FROM D_TBL)
SELECT @newc = (SELECT ID FROM inserted)
IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
IF @newc <> 0 or @newc <> null SELECT @maxc = @newc

UPDATE D_TBL SET ID = @maxc WHERE ID = @newc
GO

3) Insert a row into table D_TBL

INSERT INTO D_TBL VALUES (0, 1)
INSERT INTO D_TBL VALUES (0, 2)

SELECT * FROM D_TBL
GO
--------------------------------------------------
Returns:

ID NUM
----- -----
1 1
2 2

(1 row(s) affected)

It works fine just as expected.

4) Create a table with only one column as source table

CREATE TABLE S_TBL
( NUM INT NOT NULL
)
GO

5) Insert a row into S_TBL

INSERT INTO S_TBL VALUES (3)
GO

6) Start Data Transformation Services Import/Export Wizard,
Data Source = Microsoft OLE DB Provider for SQL Server
Destination = Microsoft OLE DB Provider for SQL Server
Use a Query to specify the data to transfer
select query builder
select all columns from S_TBL
Select Destination as D_TBL

Click Finish, the process should complete successfully!

7) SELECT * FROM D_TBL

ID NUM
---- ----
0 3
1 1
2 2

(3 row(s) affected)

Now we can see that the ID of the newest inserted row is 0, not 3 expected.
Just as you described, if I choose Microsoft ODBC Driver for SQL Server as
Destination, the expected result will return

ID NUM
---- ----
1 1
2 2
3 3

I believe with "Destination = Microsoft OLDB Provider for SQL Server", the
INSERT command is not used because it is slow, so that the trigger for
insert will no be fired. Since the trigger is not fired, every row
inserted(append) to the destination table(D_TBL) will cause the field ID to
be 0 which is by your design. ID is primary key, so we will get the error
message "Violation of PRIMARY KEY constraint".

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

Hi Terry,

If I understand correctly, you want to import 1 million records from Access
to SQL Server. However, the original records have incorrect ID values. You
want to fix it in the SQL Server table. In addition, you want to find a
quick way to do it.

Could you consider the following plan?
1. Use DTS to import Access table to SQL Server table (temptable), without
importing ID column.
2. Create a SQL Server table with all the columns in temptable, plus 1
identity column: ID. The table is table1.
3. Transfer the data in temptable into table1.

Does this plan fit in your real situation? Since I am not sure of your
exact situation, there may be misunderstanding. Please let us know if you
need further assistance.


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
<0a5001c3637f$5d7793e0$a001280a (AT) phx (DOT) gbl>
<#TPyj7vZDHA.2260 (AT) cpmsftngxa06 (DOT) phx.gbl>

Thanks to all who replied:
It appears the only way in this situation to use OLE db
and thereby be much faster than ODBC is to:
1. Find max +1 of final destination table (FDTbl)
2. Create new table (NTbl)with same columns as FDTbl with
exception of PK Column and make it an identity with SEED
of results of SELECT MAX (PK)+1 FROM FDTbl
3. DTS from Access db Source table to NTbl and let the
identity populate the column in NTbl
4. DTS from NTbl to FDTbl
5. Drop NTbl
I have created a DTS package to do everything except
SELECT MAX (PK) FROM FDTbl and place the results as the
SEED in the Create Table dbo.NTbl script.

If anyone has a suggestion on how to do this it would be
appreciated.

Terry

Hi Terry,

How about the following approach?
1. Create final destination table as one more identity column.
2. DTS from Access db Source table to a temporary table NTbl without ID
column
3. Run a SQL statement to insert FDTble from NTbl, so the identity value
populates
4. Drop NTbl

For example, considering the following 2 tables:
create table fdtbl (id int identity, testval int)

create table ntbl ( testval int)

insert ntbl values(1)
insert ntbl values(2)
insert ntbl values(3)

insert fdtbl(testval) select testval from ntbl

result of fdtbl
1 1
2 2
3 3

truncate table ntbl
insert ntbl values(4)
insert ntbl values(5)
insert ntbl values(6)

insert fdtbl(testval) select testval from ntbl

result of fdtbl
1 1
2 2
3 3
4 4
5 5
6 6


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------