Results 1 to 5 of 5

Thread: Upsize Access DB to SQL Server 2008

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Upsize Access DB to SQL Server 2008

    When Upsizing an Access 2003 databse to SQL Server 2008 how can I set my autonumber fields to convert with Identity Specification set to yes and 1?

  2. #2
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Re: Upsize Access DB to SQL Server 2008

    You can accomplish it in 2 ways:

    1. Export data to SQL Server and then manually change the NUMBER field to include identity.
    2. Create a table that already defines that field as identity, enable identity insert and do your export.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Upsize Access DB to SQL Server 2008

    This would have to be done for around 400 tables. Manually changing the field is what drove me to post a question here. Can I do this in C# programmatically?

  4. #4
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Re: Upsize Access DB to SQL Server 2008

    You can also do a straight through IMPORT into SQL Server. Select Access as your source. Then write a t-sql script to change each table to use identity. You can query sys.tables, loop through them and alter each of of them to include identity.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Upsize Access DB to SQL Server 2008

    Serge, how would I write loop to alter each of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width