Inserting data from MSAccess into sql server

L

lloydf

I would like to extract data from an MS access database table and
insert it into a SQL server table with the same structure.
I am able to populate a dataset (dsAccess) with the access data. How
can I then insert the data contained in dsAccess into the sql server
table?

Thanks in advance

LLoyd
 
B

Brendan Reynolds

A dataset? Is this a .NET question? If so,
microsoft.public.dotnet.framework.adonet might be a better place to ask.
 
C

Craig Alexander Morrison

Use SQL! not recordsets. Either use Data Transformation Services, Import
Data (DTS) in SQL Server 2000 (SQLS) or create an Append Query in
Jet/Access.

When performing set operations Recordsets should only be used to overcome
the shortcomings of any particular implementation of SQL (or of SQL itself).

Check Books Online for further info on DTS although it is incredibly easy to
understand. In Enterprise Manager (EM) click Tools and Select DTS then
Import Data. If you don't have EM with your edition of SQLS use an Access
append query.

An append query looks something like this.

INSERT INTO dbo_BusinessContact ( FirstName, SalutationName )
SELECT BusinessContact.FirstName, BusinessContact.SalutationName
FROM BusinessContact;

Where BusinessContact is the Jet table and dbo_BusinessContact is the SQLS
table linked to the Jet database.
 
C

Craig Alexander Morrison

Oh! Is that where that old term is coming from?

The term also appears in IMS (IBMs DB1?), and in OLAP (to distinguish it
from Flattened Rowsets and Recordsets).

It is possible the problem lies in the fact that the data may not have a
primary key, which is strongly advisable in Jet and -almost- mandatory in
SQLS.
 
B

Brendan Reynolds

Craig Alexander Morrison said:
Oh! Is that where that old term is coming from?

The term also appears in IMS (IBMs DB1?), and in OLAP (to distinguish it
from Flattened Rowsets and Recordsets).
Click to expand...

A dataset in ADO.NET is sometimes refered to as 'a recordset on steroids'.
It can contain multiple tables and enforce relationships between them.
Another often-used description is 'an in-memory representation of a
database'. LLoyd's use of the term together with the prefix 'ds' leads me to
suspect that he is using .NET, although of course that's just a guess.

Just to muddy the waters a little more, SQL Server Reporting Services uses
the term 'dataset' to refer to the data behind a report, although this is
not (at least not in the currently released version) an ADO.NET dataset.
It is possible the problem lies in the fact that the data may not have a
primary key, which is strongly advisable in Jet and -almost- mandatory in
SQLS.
Click to expand...

Are you thinking of some other post, Craig? I didn't see anything in LLoyd's
post that would lead me to that conclusion. I interpreted it as a 'how do
I?' type question, which is why I sought clarification on whether this is a
..NET question. If I'm right, the answer will depend to a significant degree
on the environment in which LLoyd is working - a description of how to do it
using Access and VBA might not help very much if LLoyd is using VS.NET and
VB.NET or C#! :)
 
C

Craig Alexander Morrison

Are you thinking of some other post, Craig? I didn't see anything in
LLoyd's post that would lead me to that conclusion. I interpreted it as a
'how do
Click to expand...

Datasets do not necessarily contain PKs.

Datasets are buckets in any variant of MS implementations.

Not sure whether ADO.NET is a massive backward step or not. We shall leave
it alone, for now, much as we did with the ADO passing fad.
 
C

Craig Alexander Morrison

It is possible the problem lies in the fact that the data may not have a
primary key, which is strongly advisable in Jet and -almost- mandatory in
SQLS.
Click to expand...

<<Are you thinking of some other post, Craig? I didn't see anything in
LLoyd's
post that would lead me to that conclusion>>

I meant to add that the questioner was trying to move data contained in one
Access table to one table in SQL Server.

If I am not mistaken SQL Server being updated from external data requires a
Primary Key constraint. We have been using DB2 since we found out what MS
were doing to the SQLS 2000 upgrade/downgrade so this may have been
downgraded too. (g)
 
B

Brendan Reynolds

I know what you mean, Craig, and if LLoyd had said that he had tried to
transfer the data but the transfer failed with an error message, I'd
probably be thinking along the same lines too. But he didn't say that, which
is why I wondered whether you might be thinking about a different post.
Based on what LLoyd said in his original post, I *think* the answer is that
he needs to use a second ADO.NET data adapter, connecting to SQL Server, to
write the data from the dataset into the SQL Server table. (Or change the
connection and the insert and/or update commands of the original data
adapter that he used to read the data from the Jet table into the dataset,
if preferred.) Of course it's entirely possible that you may be right and I
may be wrong - we don't have a great deal of information from which to draw
any conclusions. Perhaps LLoyd will enlighten us! :)
 
B

Brendan Reynolds

Oh, dear! I hope not - or at least not any further than the nearest ADO.NET
newsgroup - if that really is what he's using.
 
C

Craig Alexander Morrison

Warning the following message contains a rhetorical question. (g)

If you have a table in Jet and a table in SQLS and you want to move data
between them why on earth would you not use SQL?

Much a do.net about nothing, to paraphrase Billy Shakespeare.

Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
 
B

Brendan Reynolds

I know you said it was a rhetorical question, Craig, but the process I
described would, in fact, use SQL.

Now if moving the data from Jet to SQL Server is the *only* thing that LLoyd
is doing with the data, then putting it into a .NET dataset first would
certainly seem to be going the long way around. But we don't know that.
 
C

Craig Alexander Morrison

<Craig, but the process I
described would, in fact, use SQL.>
Click to expand...

Come on, the only SQL required is INSERT INTO...SELECT...FROM. Not so much a
process more a reflex.

ADO.NET is the long way round and achieves nothing that the above cannot
achieve.

<I would like to extract data from an MS access database table and
insert it into a SQL server table with the same structure>

That's what the original poster wanted to do, INSERT INTO...SELECT...FROM
end of story, and even if he wants to play with it there are other set
operators.

ADO.NET reeks of the "something must be done, this is something, let's do
it." mentality.

I will not comment further on ADO.NET and SQLS2005 as we have disavowed
these technologies, and it is not appropriate here in these newsgroups.
 
C

Craig Alexander Morrison

Well if it scared him off ADO.NET all's well that ends well. (vbg) Billy
Shakespeare again! (strewth)
 
B

Brendan Reynolds

I am at a loss to know what your point is at this stage, Craig. I certainly
have no interest in entering into any religious war controversy - the ADO
vs. DAO wars exhausted any entertainment value such controversies might once
have had for me years ago. I am merely attempting to determine what
technology the original poster is using in order to be in a better position
to provide an appropriate answer.
 
C

Craig Alexander Morrison

What religious wars are you talking about DAO, ADO, ADO.NET are all
relatively insignificant passing fads WHEN COMPARED TO SQL.

The best answer (currently and for the last 30 years) no matter what flavour
of the month (FOM) proprietary technology is being (ab)used is SQL.

If one is using Microsoft products you use DAO or JDO or ODBC Direct or ADO
or ADO.NET or FOM when you fail to do it correctly with SQL or you reach a
limitation in your products implementation of SQL or you have a general
problem with the R capabilities of SQL.

If one is not using Microsoft products what the hell are DAO, ADO, ADO.NET?
You use SQL!, or if that limits you you use their proprietary technology.

So to summarise ...INSERT INTO...SELECT...FROM, that'll do nicely.

My advice to the original poster is - you are in a hole so stop digging now.
Use SQL only.

I'll let you have the last word, if you want it.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

BTW DAO is a 15 year passing fad. (vbg)
 
B

Brendan Reynolds

A SQL statement has to be executed by something. If the original poster is
developing an Access application, he may need advice on how to execute that
SQL statement via DAO or ADO. If as I suspect he is developing a .NET
application, he may need advice on how to execute that SQL statement via
ADO.NET. I would like to know which, so that I can offer appropriate advice.

Your previous posts show you to be a knowledgeable person, Craig, and
usually a helpful and reasonable person. It is my respect for that record
that has caused me to persist in trying to understand your position in this
thread, and not any desire to 'have the last word'.

--
Brendan Reynolds (MVP)

Craig Alexander Morrison said:
What religious wars are you talking about DAO, ADO, ADO.NET are all
relatively insignificant passing fads WHEN COMPARED TO SQL.

The best answer (currently and for the last 30 years) no matter what
flavour
of the month (FOM) proprietary technology is being (ab)used is SQL.

If one is using Microsoft products you use DAO or JDO or ODBC Direct or
ADO
or ADO.NET or FOM when you fail to do it correctly with SQL or you reach a
limitation in your products implementation of SQL or you have a general
problem with the R capabilities of SQL.

If one is not using Microsoft products what the hell are DAO, ADO,
ADO.NET?
You use SQL!, or if that limits you you use their proprietary technology.

So to summarise ...INSERT INTO...SELECT...FROM, that'll do nicely.

My advice to the original poster is - you are in a hole so stop digging
now.
Use SQL only.

I'll let you have the last word, if you want it.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

BTW DAO is a 15 year passing fad. (vbg)
Click to expand...
 
Top