1

I use the import wizard for importing data from Access 2010 database into SQL Server tables.

I try to import from tblMembers which includes the most important column MiID, which is used as a FK in different other tables.

It seems that in the Access tables some records do not have a valid MiID due to former deletions of members, so the reference is broken.

When I try to import all data from tblMembers into SQL Server I get an error:

The table tblMembers cannot be truncated as there are foreign key constraints referenced to it

(translated from German, so the error description might be slightly different)

I tried to list up all table names where MiID is used as FK, as well as all constraint names, but I cannot see through what caused the problem, except the fact, that the server tables have already test data, but by ticking "delete rows in target table" (in the wizard) I think before inserting new data the existing ones are deleted and any non-matching PK/FK relation should vanish.

So, how can I solve the failing import?

What could still cause the failing?

Thanks any help Michael

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mak
  • 359
  • 3
  • 14

1 Answers1

1

You have three options:

  1. Drop the foreign key constraints in the tables in SQL Server. These appear not to be needed.
  2. Remove the rows with the offending records.
  3. Add extra members for the members that don't exist.

The third would be something like:

select t.memberid, m.col1, m.col2, . . .
from ((select t1.memberid from t1 union
       select t2.memberid from t2
      )
     ) t left join
     members m
     on t.memberid = m.memberid;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, I solved it. It was a combination of things I had to do. Drop the foreign key constraints on some tables and reseed the ID's of certain tables where the import failed. BTW, I did not know that in the wizard the "Zieltabelle löschen" means TRUNCATE TABLE and not DELETE FROM, as truncate table cuases errors when you have a foreign key constraint! Thx Michael – mak Jul 18 '17 at 14:33