In SQL Server 2008 I had remade the database structure similar to Access. I need to import a couple of related tables but I am worried that the foreign keys won't match with the autonumber fields from the related tables.
+2
A:
You have some options here:
If you export the table to SQL Server, all the data will make it through properly and then you can set your PKs and FKs
Create the Table structure with an IDENTITY column and use SET IDENTITY_INSERT to put in the values you want into the Identity column.
Without knowing more details about your table structures and locations, I can only tell you generic things like
- You will have to match the keys up manually so that the PK-FK references remain the same.
Raj More
2010-09-15 17:37:53
I think the question is rather different. You seem to be answering structurally, as though the data tables involved are empty, but I'm reading the question as saying the data already exists, and the tables being imported need to relate to tables that don't necessarily have the appropriate parent records in them. So, I see it as a data massage question -- but I could be misinterpreting it.
David-W-Fenton
2010-09-16 04:07:20
You're right - I had missed that point. I edited my answer to talk about those points as well.
Raj More
2010-09-16 14:07:42
+1
A:
If the tables are the same you could use the rather verbosely named “Microsoft SQL server Migration Assistant 2008 for Access”. This will allow you to bring over the data whilst keeping the same keys
Kevin Ross
2010-09-16 07:23:56
I think the name is "SQL Server Migration Assistant for Access v. 4.2". It works for versions of SQL Server back to 2000, if I'm not mistaken.
David-W-Fenton
2010-09-16 23:11:39
+2
A:
If you need to match the old access ids to the new autogenerated ids in an existing table, this is something you needed to do at the time of moving the data from the orginal table unless you happened to store the access ids. Usually I do some type of a cross matching table with the old id and the new id as part of the import process. Then you use this table to match to the realted tables to update their ids. If you didn;t do this and the ids are differnt, you will have to find a way to match them to the orginal access table first before you can import the related tables. I hope your table has a natural key in that case.
HLGEM
2010-09-16 14:12:43