액세스 INSERT INTO - aegseseu INSERT INTO

Bob Barker

unread,

Mar 9, 2010, 2:30:38 PM3/9/10

to

I am attempting to reduce the number of harddisk writes I make into a
mdb file. Currently, I insert roughly 10,000 rows of data on a daily
basis into this networked mdb-file, but it takes 1 hour to do so which
is now becoming cumbersome to our business processes. I have
identified the time bottleneck is caused by the number of harddisk
writes necessary to get 10,000 rows of data.

Currently, I am inserting 1 row at a time because I am parsing a text
file to extract data from it. I am attempting to change the 1-row-
insert into a MASS INSERT or BULK INSERT into the table to reduce the
number of INSERT queries I run. Something to the the tune of

INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
( (1,2,3,4) , (4,3,2,1), (5,5,5,5));

Apparantly, the above statement is NOT supported by MS Access! How
can I do a mass insert? After googling endlessly, I now know MS
Access can do a mass insert using the SELECT keyword such as

INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
SELECT Field1, Field2, Field3, Field4 FROM tblWTF

However, because I am parsing a text file and storing the individual
rows in memory, there is no tblWTF. There is no point in creating
this table either since now I'll be doing the same number of writes to
the database + 1. In this case, I have to individually place the
parsed rows into tblWTF then move it to tblTest using that "INSERT-
SELECT" method. This isn't what I want to do--my objective is to
reduce the # of writes I make to the HD because it's causing lots of
time problems. Any suggestions?

Marshall Barton

unread,

Mar 9, 2010, 3:22:03 PM3/9/10

to

Bob Barker wrote:

Have you tested using a recordset with the AddNew/Update
methods to add the records. I suspect that it will cache
the records and write them in batches or at least give you
more control of the writes (by closing and reopening the
recordset. Just make sure that you open the recordset using
Where False so you don't waste time retrieving existing
records.

That may also help because it keeps the connection to the
back end open. Using separate append queries one at a time
waste a significant amount of time re-establishing the
connection for each append.

Another thing that may(?) help is to remove all indexes on
the table, append all the records and then recreate the
indexes.

--
Marsh

paii, Ron

unread,

Mar 9, 2010, 3:37:34 PM3/9/10

to

"Marshall Barton" <> wrote in message
news:...

Doing the inserts within a transaction may also help.

Albert D. Kallal

unread,

Mar 9, 2010, 6:02:58 PM3/9/10

to

I don't think inserting 10,000 rows should take more then about 1 or 2
minutes.

Open a reocrdset....don't use separate sql insert commands. Using inserts
will likely be 100, if not a 1000 times slower then a reocrdset...

So, try a reordset...open the reocrdset once..and use add new....

Also, test your processing loop without actually doing ANY inserts to find
out if you have any bottle necks in the code that processes or prepares the
data before you attempt to insert into the table.

Post back here...the insert process will likely only take a minute or two as
10,000 records is not much at all..

So open a reocrdset....keep it open, and use addnew for each row...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Salad

unread,

Mar 9, 2010, 6:12:21 PM3/9/10

to

10,000 records is a few records to be sure but taking 1 hour to process?
I suppose you can blame it on harddisk writes. It sounds like a good
enough explanation to give to someone in management.

We'll assume you can't link to text file to the mdb and append from
that. Maybe its a report from a mainframe and you need to parse out the
page breaks etc to get the data first.

Maybe you could
Open "OUTFILE" For Output As #2 ' Open file for output.
Open "INFILE" For Input As #1 ' Open file for output.
and use Input, Input #, or Line Input# to read the data and use Print #,
Write #, Write, or WriteLine to create a the parsed text file. Then
write a SQL statement to append the data from the Outfile text file or
use TransferText to populate the table.

The Frog

unread,

Mar 10, 2010, 3:38:44 AM3/10/10

to

Hi Bob,

I use a disconnected ADO recordset when doing bulk inserting. I create
a recordset based on the target table, then close the connection -
there is no 'actual' writing taking place at this time.

Next I start a transaction with the recordset, add the records I want,
then re-open the connection, and commit the transaction / update.
Doing this I am able to insert over 8 million records across a network
(10 base T would you believe) in just under ten minutes.

I firmly believe that a recordset is your way to go. Having it
disconnected also allows you to run further checks and manipulations
on the inserted data if you want to before actually writing it to
disk.

If you would like to post a little more of the code and sql that you
use I am sure that there will be a way to speed up the processing you
have and hence reduce the time required. This is a pretty normal thing
for my work and I am happy to help. Please remember that the above is
just an example and we would need to know some more about your
scenario to help further.

Cheers

The Frog

Bob Barker

unread,

Mar 10, 2010, 2:02:06 PM3/10/10

to

Thank you all who responded thus far!

In my test environment I set up, I was able to insert 1,000,000
records in 3 minutes and 39 seconds using the RecordSet method. I
have yet to transport this into a production or even a development
version of a production database we use, but so far this is
promising! 10,000 records took me 1 second approximately! I am very
excited.

Some issues I ran into: First: I was sending a CommandType Enum
option into the RecordSet open method "adCmdTable"
rst.Open "tblTest", CurrentProject.Connection,
adOpenForwardOnly, adLockOptimistic, adCmdTable
but this caused a 3001 runtime error when attempting to invoke the
AddNew method. After googling some more, MSFT support solution said
to change the CommandType Enum to adCmdTableDirect so my Open method
became
rst.Open "tblTest", CurrentProject.Connection,
adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
After some testing, it took 8 minutes+ to insert 14,651 records which
was an improvement but still not a lot of time savings. I began
focusing on the commandtype enum and why I even needed to send this
since it was optional. I found out that the Open method queries the
connection to the DB and determines which CommandType method is most
optimal to use. After omitting the option at the end, my results are
as folows:

10,000 ROWS - 1 Average
1,000,000 ROWS - 3 minutes 39 seconds Average.

Each row contained a String of variable length between 2 and 50, 1
Integer, a Short Date, and a Double (AKA Float).

THANK YOU ALL! ONCE AGAIN!

gregharward

unread,

Jun 6, 2010, 1:53:11 PM6/6/10

to

The fastest method is to use Indexed Sequential Access Method - ISAM. This allows for directly transfering data from your connected ADO record source to a second dynamically connected datasource. I figured out how to do this this week and it is the only way to fly! Do yourself a favor any try this, you will be glad that you did!

1. Use an ADODB.Connection to connect to ANY ADO DB using the standard connection strings (www.connectionstrings.com).
2. Once connected, in the SQL query instead of entering a destination table name use an ISAM name to interact with a seperate DB.
3. Note that the Excel drivers work for BOTH 2003 and 2007 even though the version says 8.0 (it is not 12.0). This threw me for a bit as 8.0 is how to specify 2003 in a connection string.
4. Using this method I am able to transfer data between any two data sources (since the initial connection can by to any datasource) using a single SQL statement including CSV, Excel 2003/2007, Access 2003/2007, SQL Server. I think I have even seen a DB2 example, so others are be possible.

Here is the syntax:

SELECT * INTO <Put ISAM Name Here> FROM <normal query syntax to connected DB and table>
INSERT * INTO <Put ISAM Name Here> FROM <normal query syntax to connected DB and table>
or
SELECT * INTO <normal query syntax to connected DB and table> FROM <Put ISAM Name Here>
INSERT * INTO <normal query syntax to connected DB and table> FROM <Put ISAM Name Here>

...other options are possible, the point is just to replace the table name with the ISAM Name in the query.

ISAM Names:

CSV & TXT
[Text;Database=C:\_Path\;HDR=Yes].[File.csv]
[Text;Database=C:\_Path\;HDR=Yes].[File.txt]

Access 2003 & 2007
[MS Access;Database=C:\Path\File.mdb].[AccessTableName]
[MS Access;Database=C:\Path\File.accdb].[AccessTableName]

Excel 2003 & Excel 2007
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xls].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsx].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsm].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsb].[Table$]

SQL Server
[ODBC;Driver=SQL Server;SERVER=<Server Name>;DATABASE=<Database Name>;UID=<UserName>;PWD=<password>;].[Table]

Here are some links:
http://support.microsoft.com/kb/321686
http://support.microsoft.com/kb/200427

Additionally, if you would like to create a file based CSV, Excel 2003/2007, Access 2003/2007 database via the ADO connection, that can be done using the ADOX object.

Set oADOXDatabase = New ADOX.Catalog

oADOXDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & <path to file>)

The combination of these concepts allows for the creation and population of a DB file using ADO very quickly.

I have working code for this, so I can tell you that it works great if you feel the need...the need for speed!

Greg Harward

Bob Barker wrote:

Thank you all who responded thus far!

10-Mar-10

Previous Posts In This Thread:

On Tuesday, March 09, 2010 2:30 PM
Bob Barker wrote:

MS Access Mass/Bulk Insert into a table

I am attempting to reduce the number of harddisk writes I make into a
mdb file. Currently, I insert roughly 10,000 rows of data on a daily
basis into this networked mdb-file, but it takes 1 hour to do so which
is now becoming cumbersome to our business processes. I have
identified the time bottleneck is caused by the number of harddisk
writes necessary to get 10,000 rows of data.

Currently, I am inserting 1 row at a time because I am parsing a text
file to extract data from it. I am attempting to change the 1-row-
insert into a MASS INSERT or BULK INSERT into the table to reduce the
number of INSERT queries I run. Something to the the tune of

INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
( (1,2,3,4) , (4,3,2,1), (5,5,5,5));

Apparantly, the above statement is NOT supported by MS Access! How
can I do a mass insert? After googling endlessly, I now know MS
Access can do a mass insert using the SELECT keyword such as

INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
SELECT Field1, Field2, Field3, Field4 FROM tblWTF

However, because I am parsing a text file and storing the individual
rows in memory, there is no tblWTF. There is no point in creating

this table either since now I will be doing the same number of writes to

the database + 1. In this case, I have to individually place the
parsed rows into tblWTF then move it to tblTest using that "INSERT-

SELECT" method. This is not what I want to do--my objective is to
reduce the # of writes I make to the HD because it is causing lots of
time problems. Any suggestions?

On Tuesday, March 09, 2010 3:22 PM
Marshall Barton wrote:

Bob Barker wrote:Have you tested using a recordset with the
Bob Barker wrote:

Have you tested using a recordset with the AddNew/Update
methods to add the records. I suspect that it will cache
the records and write them in batches or at least give you
more control of the writes (by closing and reopening the
recordset. Just make sure that you open the recordset using

Where False so you do not waste time retrieving existing
records.

That may also help because it keeps the connection to the
back end open. Using separate append queries one at a time
waste a significant amount of time re-establishing the
connection for each append.

Another thing that may(?) help is to remove all indexes on
the table, append all the records and then recreate the
indexes.

--
Marsh

On Tuesday, March 09, 2010 3:37 PM
paii, Ron wrote:

Doing the inserts within a transaction may also help.
Doing the inserts within a transaction may also help.

On Tuesday, March 09, 2010 6:02 PM
Albert D. Kallal wrote:

I do not think inserting 10,000 rows should take more then about 1 or 2minutes.
I do not think inserting 10,000 rows should take more then about 1 or 2
minutes.

Open a reocrdset....do not use separate sql insert commands. Using inserts

will likely be 100, if not a 1000 times slower then a reocrdset...

So, try a reordset...open the reocrdset once..and use add new....

Also, test your processing loop without actually doing ANY inserts to find
out if you have any bottle necks in the code that processes or prepares the
data before you attempt to insert into the table.

Post back here...the insert process will likely only take a minute or two as
10,000 records is not much at all..

So open a reocrdset....keep it open, and use addnew for each row...

On Tuesday, March 09, 2010 6:12 PM
Salad wrote:

Bob Barker wrote:10,000 records is a few records to be sure but taking 1 hour
Bob Barker wrote:

10,000 records is a few records to be sure but taking 1 hour to process?
I suppose you can blame it on harddisk writes. It sounds like a good
enough explanation to give to someone in management.

We'll assume you cannot link to text file to the mdb and append from

that. Maybe its a report from a mainframe and you need to parse out the
page breaks etc to get the data first.

Maybe you could
Open "OUTFILE" For Output As #2 ' Open file for output.
Open "INFILE" For Input As #1 ' Open file for output.
and use Input, Input #, or Line Input# to read the data and use Print #,
Write #, Write, or WriteLine to create a the parsed text file. Then
write a SQL statement to append the data from the Outfile text file or
use TransferText to populate the table.

On Wednesday, March 10, 2010 3:38 AM
The Frog wrote:

Hi Bob,I use a disconnected ADO recordset when doing bulk inserting.
Hi Bob,

Cheers

The Frog

On Wednesday, March 10, 2010 2:02 PM
Bob Barker wrote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET GridView: Select Row and Display Item Detail
http://www.eggheadcafe.com/tutorials/aspnet/ff14a008-2af9-4f9d-a09d-1af670466a80/aspnet-gridview-select.aspx

Phillip Holmes

unread,

Jun 6, 2010, 6:09:03 PM6/6/10

to

MCSDPhil
Hi there,
I may be missing something here, but is it not possible just to import
the data straight into Access? As long as the data is delimited in some
way this should be fairly quick and painless.
Regards, Phil.

*** Sent via Developersdex http://www.developersdex.com ***