By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Moving data to SQL Server

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.