Database fun...

Results 1 to 2 of 2

Thread: Database fun...

  1. #1
    Sean Guest

    Default Database fun...

    Ok, this is a fun one, fo me anyway. I am writing a script to dump data from one database to another one, similar fields and layout. The problem is, the databases hae multiple tables in them, and these tables have records that points to other records by way of ID numbers in other tables in the database. all well and good, except the ID field we use(the primary key) is an autonumber, and when the old data is inserted into the new database, it gets a new ID. Is there any way I can either a)keep the records pointing to each other with the new IDs or b)somehow retain the old IDs even with the autonumber primary key? adding another ID field was ousted by the bossm so I'm a little stuck...

  2. #2
    Join Date
    Dec 1969

    Default What DB are you using?

    If you are in SQL Server 7.0/2000 then you are in like Flynn using the DTS Package capability. A simple but GREAT tool. You can do identity inserts on transformations, or simply guide relationships in to maintain ref. integrity on all foreign keys (although that takes some planning and testing if you have a complex structure). If you are in Access or something else (judging by your use of Autonumber I assume this to be the case) I believe your best bet will be to write a set of queries and carefully migrate or you could always denormalize the data (merge to a very redundant master table) then normalize it again into the final destination. This depends on the frequency of this request and how many tables we're talking. If it is frequent and large - my opinion would lean toward your first suggestion - just tell him it's a compound primary key! (or get SQL Server ASAP) Good luck!

Posting Permissions

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