Importing Data from MS Access to SQL Server

[Weste]

I am trying to import data from Access into SQL Server. The primary key on
most of the tables is an Autonumber in Access and an Identity Specification
in SQL Server. The queries use INSERT INTO...SELECT...FROM syntax. I
receive a key violation when running the queries. If I follow the steps
below everything works fine.

1. Remove Identity Specification from SQL Server tables
2. Load data using queries with INSERT INTO...SELECT...FROM syntax
3. Add Identity Specification back to SQL Server tables

Is there a better way to load the data that doesn’t require removing the
Identity Specification and then adding it back? Thanks for your help.

[Alex Dybenko]

Hi,
have you tried to use upsizing wizard? I will do then whole thing for you.
if no - then you can turn on identity insert for a table, see SQL Server
books online for more info
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Weste
I am trying to import data from Access into SQL Server. The primary key on
most of the tables is an Autonumber in Access and an Identity
Specification
in SQL Server. The queries use INSERT INTO...SELECT...FROM syntax. I
receive a key violation when running the queries. If I follow the steps
below everything works fine.
1. Remove Identity Specification from SQL Server tables
2. Load data using queries with INSERT INTO...SELECT...FROM syntax
3. Add Identity Specification back to SQL Server tables
Is there a better way to load the data that doesn’t require removing the
Identity Specification and then adding it back? Thanks for your help.

[Mary Chipman [MSFT]]

If you want to keep the original ID from the Jet table and not have
SQLS create a new one for you, then no. You have to disable the
generation of a new identity column value, insert the data with the
existing ID, then turn it back on again. If you need to do this
repeatedly and you are initiating the operation from Access, then I'd
suggest writing code to automate the process. You can send commands to
SQLS executing a pass-through query (DAO.querydef.SQL) then run the
Access query then run the pass-through query to turn identity back on
from VBA code.

--Mary

On Thu, 10 Jan 2008 18:57:41 -0800, Weste

Post by Weste
I am trying to import data from Access into SQL Server. The primary key on
most of the tables is an Autonumber in Access and an Identity Specification
in SQL Server. The queries use INSERT INTO...SELECT...FROM syntax. I
receive a key violation when running the queries. If I follow the steps
below everything works fine.
1. Remove Identity Specification from SQL Server tables
2. Load data using queries with INSERT INTO...SELECT...FROM syntax
3. Add Identity Specification back to SQL Server tables
Is there a better way to load the data that doesn’t require removing the
Identity Specification and then adding it back? Thanks for your help.

[unknown]

If need to do this importation repeatedly, then are you sure that you need
this identity field on the SQL-Server or that you couldn't be best served by
using a separate table for the data coming from Access or even using a
linked view from SQL-Server to Access?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Weste
I am trying to import data from Access into SQL Server. The primary key on
most of the tables is an Autonumber in Access and an Identity
Specification
in SQL Server. The queries use INSERT INTO...SELECT...FROM syntax. I
receive a key violation when running the queries. If I follow the steps
below everything works fine.
1. Remove Identity Specification from SQL Server tables
2. Load data using queries with INSERT INTO...SELECT...FROM syntax
3. Add Identity Specification back to SQL Server tables
Is there a better way to load the data that doesn't require removing the
Identity Specification and then adding it back? Thanks for your help.

[d***@epsomdotcomdotau]

Are you running the tables in the right order?

Post by Weste
I am trying to import data from Access into SQL Server. The primary key on
most of the tables is an Autonumber in Access and an Identity

Specification

Post by Weste
in SQL Server. The queries use INSERT INTO...SELECT...FROM syntax. I
receive a key violation when running the queries. If I follow the steps
below everything works fine.
1. Remove Identity Specification from SQL Server tables
2. Load data using queries with INSERT INTO...SELECT...FROM syntax
3. Add Identity Specification back to SQL Server tables
Is there a better way to load the data that doesn't require removing the
Identity Specification and then adding it back? Thanks for your help.