Converting from Access to SQL Server, a GenericDB User's Guide

Abstract:

The document assists users migrating from Microsoft Access to Microsoft SQL Server. Though written specifically for users of GenericDB, the process may be applied to any Access database.

Of particular note are the instructions to convert a Random Autonumber field to one which uses Incremental Autonumbers prior to migrating data. Such fields are mainly used as Primary Keys. SQL Server does not natively support randomly generated record ID's, but does support automatic incremental ID's with the IDENTITY property. When one simply moves a table into SQL Server and sets the key field to IDENTITY, SQL Server attempts to pick up the numbering with the next highest value, which may be near the upper bounds of an integer field type, effectively limiting the growth of the table.

Steps included in this document help a user convert an Autonumber table key from Random to Incremental, while maintaining referential integrity with any Child tables. The basic technique could also be used to convert GUIDs or any other Primary Key scheme to Incremental Autonumber.

The instructions assume little prior knowledge and include both one-time setup steps and conversion techniques which many databases do not need. Once you get the hang of it, the actual transfer process is pretty fast, a matter of minutes for most tables.

This is version 0.1 of this document, feedback is encouraged.

View Complete Article (Note, this is not yet a permanent URL, contact me if you would like to be notified when a permanent URL is available for this document.)