Post reply

Export Data (only) between databases

  • May 3, 2007 at 2:37 am

    #62653

    Hi All,

    anyone knows how its possible to export only data from databases through DTS Import/Export Wizard?

    If I only choose Copy data option (without Create destination objects option) I always have an error about "cannot truncate table xxx because it is being referenced by a FOREIGN KEY constraint". It's usual to have foreign keys into databases.

    I don't want to transfer objects from source to destination.

    Thanks

  • May 3, 2007 at 8:42 am

    #704098

    This isn't transferring objects, it's trying to clear out all data, but you have FK references to/from another table, so you cannot do this.

    Can you explain more about the table you are trying to xfer?

  • May 3, 2007 at 9:24 am

    #704117

    I've found that it's easier to create a DTS package that includes tasks that drop FK constraints, copy data, and recreate FK constraints.  The wizard doesn't give you any way to disable or drop FKs or to specify the order in which tables are imported.

    The idea here is that you can't remove data from a referenced table without first removing data from the referencing table unless the foreign key constraints were created with cascading referential integrity.

    Greg

    Greg

  • May 4, 2007 at 1:27 am

    #704288

    Hi,

    I don't want to attach/detach the database or backup/restore it because I have differents security users or groups in both servers and then I only wanted to transfer the Data.

    I thought that if I only chose the Copy data option I could transfer only data. I don't need to transfer all objects because I have them in the destination database.

    Then, I suppose that the better way is to choose Create destination objects and Copy data options and I don't enable the security options (users, logins and object level permisions). And after I manage permissions again in all objects with the destination database users.

    Thanks.

  • May 4, 2007 at 9:18 am

    #704380

    Please see my previous post.  You can transfer only data.  You just have to work within the limitations of the foreign key constraints.  Look up "foreign keys" in BooksOnLine for more info.

    Greg

    Greg

  • May 4, 2007 at 10:02 am

    #704406

    Here's a pretty comprehensive bit on disabling/re-enabling foreign keys and triggers:

     

    http://msdn.microsoft.com/msdnmag/issues/07/04/DataPoints/

     

     

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

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