Easy way to copy records from one table to another in code?

[Maury Markowitz]

I have a table on a slow database that I need to work with all the
time. In order to improve performance, I cache the columns we use a
lot on our local SQL Server.

In an MDB I can INSERT into my local table using a SELECT from the
remote one. I'm trying to duplicate this under an ADP, but so far all
I can come up with is looping over the rows. I open a connection to
the slow DB using a static read-only ODBC link, another to my local
table, and then loop over the first inserting individually into the
second.

Can anyone suggest an faster way to do this?

Maury

 

[Jeff Boyce]

Maury

Possible reasons why an Access db/table might have "poor" performance:

* impatience - some folks are, some aren't
* indexing - if searching/joining/selecting on fields, indexing may speed
it up
* network - is the db "split"?
* antivirus - local or network
* re-establishing table connections

.... and many more.

?Any of these fit your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

I have a table on a slow database that I need to work with all the
time. In order to improve performance, I cache the columns we use a
lot on our local SQL Server.

In an MDB I can INSERT into my local table using a SELECT from the
remote one. I'm trying to duplicate this under an ADP, but so far all
I can come up with is looping over the rows. I open a connection to
the slow DB using a static read-only ODBC link, another to my local
table, and then loop over the first inserting individually into the
second.

Can anyone suggest an faster way to do this?

I'm not very conversant with ADPs, but can you add the other database as a
linked server, and then run a simple INSERT query to copy from it to your
local table?

 

[aaron.kempf]

I'd just use DTS to copy the table that you want.. every 5 minutes, or
something like that

SQL Server is always faster than Jet.

-Aaron

 

[aaron.kempf]

and you do know that you can use select into-- in SQL Server-- just
like you can in Access, right?


Select *
into Local_Employees
From Northwind.dbo.Employees

 

[Maury Markowitz]

I'm not very conversant with ADPs, but can you add the other database as a
linked server, and then run a simple INSERT query to copy from it to your
local table?

I've been thinking of going down this road for a while -- we've spoken
about this possibility several times in the past. But examples are
thin on the ground. MS's own documentation is EXTREMELY thin, even
their page on "Identifying a Data Source Using a Linked Server Name"
doesn't give a single real-world example! I've found a few examples
out on the 'net, but they are all really lightweight. I'm worried that
this isn't actually used very often in the real world, except for
distributed query operations. Is this stuff production quality?

Maury

 

[Jeff Boyce]

Maury

The answers will depend on how your terms are defined.

In my situation, I have several Access front-end/SQL-Server back-end/linked
tables via ODBC applications running in support of basic
infrastructure/operations at the agency I work for. Does that match your
definition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm not very conversant with ADPs, but can you add the other database as a
linked server, and then run a simple INSERT query to copy from it to your
local table?

I've been thinking of going down this road for a while -- we've spoken
about this possibility several times in the past. But examples are
thin on the ground. MS's own documentation is EXTREMELY thin, even
their page on "Identifying a Data Source Using a Linked Server Name"
doesn't give a single real-world example! I've found a few examples
out on the 'net, but they are all really lightweight. I'm worried that
this isn't actually used very often in the real world, except for
distributed query operations. Is this stuff production quality?

Maury

 

[Paul Shapiro]

When I last used linked servers some years ago with SQL Server 2000 they
worked fine, especially for transferring data. They would be very slow if
you were doing a join between tables in different servers, but that seems
reasonable.

I think a few things have changed. There were substantial security
deficiencies with linked servers. If I remember correctly the account was
defined in the link, and the word is a lot more security/control conscious
now than it used to be.

Data Transformation Services (DTS) and then SQL Server Integration Services
(SSIS) became very capable tools for doing data transfer between servers,
with very substantial capabilities. That's what I've used more recently. But
linked servers probably still work just fine and I don't remember it as
being complicated. Basically you're defining a data connection.

I'm not very conversant with ADPs, but can you add the other database as a
linked server, and then run a simple INSERT query to copy from it to your
local table?

I've been thinking of going down this road for a while -- we've spoken
about this possibility several times in the past. But examples are
thin on the ground. MS's own documentation is EXTREMELY thin, even
their page on "Identifying a Data Source Using a Linked Server Name"
doesn't give a single real-world example! I've found a few examples
out on the 'net, but they are all really lightweight. I'm worried that
this isn't actually used very often in the real world, except for
distributed query operations. Is this stuff production quality?

Maury