Access 2k -> SQL Server 2k
My client has an app that is A2k FE with A2k BE. They have asked me
to move the BE to SQL Server.
I have a bit of experience with SQL Server, and I'm happy with
scripting the database etc.
However, when it comes time to move the data itself, I have a teensy
little concern.
Let's take our typical Customers -> Orders relation, where CustomerID
is the Foreign Key in the Orders table.
Let's say I have 4 customers, but I used to have 6. The CustomerID is
an AutoNum column, and they are 1, 2, 4, 6. However, when I insert
these records into the matching SQL Server table with an Identity
column, they will (presumably) be 1, 2, 3, 4.
So what can I do about the matching orders? Is there any alternative
to this, which seeems very long winded.
1. Insert the records as above, but copy the OLD Access AutoNum
column (called OldCustomerID) into a temporary column in the new SQL
Server table for both the Customers and Orders tables.
2. Insert the Orders records into the new SQL Server table.
3. Run an update query thus:
UPDATE O
SET CustomerID = C.CustomerID
FROM Orders O
INNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerID
Can I do this any easier? For example, is there any way in SQL Server
to maintain the values of the AutoNum field in the Insert but have
them still be Identity fields? I know that I can import the AutoNum
data into a simple int column, and once the import is complete, turn
ON the identity, but is this reliable? Might I lose any data
integrity?
TIA
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk