Am I missing something??

Question

Am I missing something??

Neville Turbit

Grasshopper

Points: 14
More actions
October 23, 2007 at 11:55 pm

#179526

I seem to be missing something in using DTS for data import. I am using SQL Server 2000 to load an Access 2000 database. When I first ran the import, I copied 47 tables and had 22 table errors. I re-ran it for the 22 errors and this time had 13 tables successful and 9 errors. After the fourth run, all were successful.
The reason was foreign key errors. The table containing the PK in a relationship had not been loaded at the point the foreign key table was loaded. Consequently there was a foreign key in the foreign key table without a matching primary key in the primary key table.
Is this normal, or, because I am just learning SQL Server, am I missing something.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 1

Does your access db has been normalised. Does your design of the sql db has been related properly. If your sql db has been normalised with proper relationships then chances for dts import data mechanisms should'nt be failures.

Better take the script of the Data using database publishing wizard & run through the same with sql query analyzer

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 2

I think that the problem is the sequence of importing tables.

Referenced tables need to be imported before referencing tables can be.

okhere SSC-Addicted Points: 443 More actions · Answer 3

Hi,

You seem to be able to identify which tables have the foreign keys ... Try to divide the job into several copies: First copy the tables that have NO FOREIGN KEYS because they will probably copy fine. Next, copy the next group of tables that HAVE FOREIGN KEYS of ALREADY COPIED tables.

This way, as you do the copy, you are assured that with a given number of steps, copying each table once only, the whole operation is a success.

For example: If I am copying 3 tables: STUDENT, COURSE and STUDENT COURSE (This table contains the foreign keys of STUDENT AND COURSE), I would have 2 steps:

1. Copy STUDENT and COURSE

2. Copy STUDENT COURSE

I hope that helps,

Osama

Neville Turbit Grasshopper Points: 14 More actions · Answer 4

Thanks for the feedback. Seems the approach mentioned will solve the problem.