Post reply

Import Export Wizard SQL SERVER 2005

  • April 28, 2008 at 11:18 am

    #121272

    Dear Friends,

    When i try to use Import/Export Wizard and delete rows in destnation table i got the following message but when i delete rows in destnation table manualy( delete from orders) then use Import /Export wizard agin with option append rows to destaination table it's copy successfully.

    that's for me problem with TRUNCATE by Wizard.. so why thats happen now

    in sql 2000 it's work fine.

    Note : I modify Foregin key

    Foregin key enforce replication :No

    Foregin key enforce constraint :No

    this is error :

    Operation stopped...

    - Initializing Data Flow Task (Success)

    - Initializing Connections (Success)

    - Setting SQL Command (Success)

    - Setting Source Connection (Success)

    - Setting Destination Connection (Success)

    - Validating (Error)

    Messages

    * Error 0xc002f210: Preparation SQL Task: Executing the query "TRUNCATE TABLE [Northwind].[dbo].[Orders]

    " failed with the following error: "Cannot truncate table 'Northwind.dbo.Orders' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    (SQL Server Import and Export Wizard)

    - Prepare for Execute (Stopped)

    - Pre-execute (Stopped)

    - Executing (Success)

    - Copying to [Northwind].[dbo].[Orders] (Stopped)

    - Post-execute (Stopped)

    - Cleanup (Stopped)

  • April 29, 2008 at 3:12 am

    #808191

    Any one can help.

  • April 29, 2008 at 7:27 am

    #808313

    When you removed the rows manually, you used DELETE FROM which will work on a table referenced by a foreign key constraint, while the Import/Export Wizard uses TRUNCATE TABLE. As the message states, TRUNCATE won't work on a table that's being referenced by a foreign key constraint.

    Since you can't control whether the Wizard uses DELETE or TRUNCATE, you'll need to drop the FK constraints before truncating and importing, then recreate the constraints afterward.

    Greg

  • August 6, 2009 at 10:07 am

    #1035506

    So what is the best way to:

    1) Capture all Constraints including FK Constrints in SQL for every table in a DB and dropping them

    2) Import new data

    3) Re-Create all Constraints including FK Constraints

    Keep in mind that Source Table's have unique ID's which need to be maintained as there propagated to Destination Table's as well.

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

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