Re: Dealing with transfer of auto increment columns?

Ewald B?rger <mssecurity_at_ewald.hetnetniet.nl> wrote:
: Hi,

Howdy!

: I'm having a problem which basically comes down to "how to keep
: relational integrity when transferring tables with references to a new
: database".

: The context for this is a tool I'm writing to be able to transfer a
: complete database (in this case MS SQL Server) to a XML file, this is
: the simple half of the tool. The other half of the tool (after
: distributing this XML file on a cd or by email) should be able to
: reconstruct this database in the same or another RDBMS (target at this
: moment is Oracle).
: The problem primarily lies in the fact that the source database uses a
: lot of auto increment columns to make ID's and those ID's are referenced
: in other tables.
: An extension of this problem is the update of an existing database in
: the same way (using a XML file), but then problem isn't directly the
: auto increment, but the possible overlap in ID's.

I think both the problem and solution here is that MSSQL and Oracle use different key generation facilities. MSSQL uses auto-increment, Oracle uses sequences. My recommendation is that you import all the MSSQL data, preserving the existing IDs, then create the sequences afterwards with 'START WITH' clauses set above your highest ids. Received on Wed Sep 19 2001 - 16:29:06 CEST