SQL Server Performance

Import/Export Data

Discussion in 'SQL Server 2008 General DBA Questions' started by ismailadar, Dec 29, 2010.

  1. ismailadar New Member

    hi all,
    When i try to import or export data from a database an error appears if destiation table has a foreign key.Cause I need to delete existing rows before Import/Export Data but when wizard try to truncate table before Import/Export Data it does not achieve this?
    What is the best way of Import/Export Data with multiple tables which has reletaionship between two or more? I try to drop constraint after Import/Export Data has finished i create them again.
    But it is to diffucult to make it so i have search the best way for Import/Export Data?
    thanks in advance
  2. satya Moderator

    How frequently you are running this data import?
    The way I see the problem is data that is loaded to a table has Foreight Key constraint where not in corresponding table with a reference.To workout either load the data into the tables in an order where any tables with
    referenced columns on FK constraints are loaded first, or you can disable
    the constraint and re-enable it after the load.
    Check BOL for WITH CHECK and WITH NOCHECK constraint related information. Also see http://books.google.co.uk/books?id=...&resnum=5&ved=0CDMQ6AEwBA#v=onepage&q&f=false for reference.
  3. ismailadar New Member

    Thanks for your reply I think the easiest way to achieve this disable constraint before starting import/export data.I decided to use sp_msforeachtable SP in oreder to disable all constraints sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
  4. satya Moderator

    Yes it may work to some extent, but think about data consistency if you disable all the CHECK constraints in the database.
    I suggest to test on dev platform to avoid any surprises on production environemnt.
  5. ismailadar New Member

    i have just tested it in a dev enviroment thanx for your replies.

Share This Page