Moving data to SQL Server

[Edward]

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

[DCM Fan]

<< I know that I can import the AutoNum
data into a simple int column, and once the import is complete, turn
ON the identity>>

That's the recommended solution.

1) Use the Access upsize wizard to transfer the structure ONLY

2) Change the SQL db's IDENTITY fields (AutoNumber in Access) to int fields.

3) Using DTS, transfer the contents of the Parent and lookup-type tables (do
not include any child tables)

4) Transfer the child tables in the appropriate Foreign key order.

5) when all of the data has been transferred, set int fields back to IDENTITY.

EX: In the Customer>Order>Detail<Product relation, I'd transfer the Customer
and Product tables in step 3, then Order, then Detail.

NOTE: Depending on the number of tables, it might benifit you to REMOVE any
Foreign Key constraints, import ALL of the data at once (basically, we'd be
treating the tables as non-related entities), and then put the FKs back in
there afterward.

[DFS]

Edward,

You have to turn identity insert on and off when adding values to a SQL
Server identity field.

SET IDENTITY_INSERT TableName ON;
.... issue your inserts
SET IDENTITY_INSERT TableName OFF;

This will maintain your existing Access AutoNumber IDs.

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

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

[DFS]

Forgot to say something, which I'm sure you know: even with IDENTITY_INSERT,
you still have to populate your tables in the correct order so as not to
violate referential integrity. Parent table first, then child, etc.
"DFS" <no****@nospam.com> wrote in message
news:vu************@corp.supernews.com...

Edward,

You have to turn identity insert on and off when adding values to a SQL
Server identity field.

SET IDENTITY_INSERT TableName ON;
... issue your inserts
SET IDENTITY_INSERT TableName OFF;

This will maintain your existing Access AutoNumber IDs.

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

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

[Robert Glöckner]

Unfortunately there is a bug in MSSQL 2000 so that 'set identity_insert on'
does not work as expected.

My company managed to get a hotfix from Microsoft, so see if you can get it
too or use the other method from the article before

Regards, Robert

--

"DFS" <no****@nospam.com> schrieb im Newsbeitrag
news:vu************@corp.supernews.com...

Edward,

You have to turn identity insert on and off when adding values to a SQL
Server identity field.

SET IDENTITY_INSERT TableName ON;
... issue your inserts
SET IDENTITY_INSERT TableName OFF;

This will maintain your existing Access AutoNumber IDs.

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

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