Post reply

Keep Identity values when importing data from one sql server table to another

  • May 5, 2008 at 8:41 am

    #121528

    I use the identity insert on value when importing data from multiple tables. But when I look at the data it's importing it is not keeping the identity field values. If I only import one table at a time it works but If I try importing multiple tables it doesn't.

    Any one else had this problem?

  • May 5, 2008 at 10:31 am

    #810800

    Are you using SET IDENTITY_INSERT ON for each table? Only one table at a time can have it on.

    Greg

  • May 5, 2008 at 10:36 am

    #810803

    Yes. So you can only import one table at a time with Identity insert on? So If I need to import 50 tables I have to do each of them individually.

  • May 5, 2008 at 10:55 am

    #810807

    You have to turn identity insert on and off as each table is loaded. Of course that will slow you down because you will only be able to load one table at a time.

    Have you considered taking a backup of server and and restoring to server b? That might be useful if server a contains only the data you want on server b.

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • May 6, 2008 at 8:51 am

    #811264

    You can still import all the tables in a single package with precedent constraints controlling flow between data flow tasks and it will still be pretty fast.

    Greg

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

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