Wednesday, May 19, 2010

Insert multiple records to Microsoft Access database

Did not find a simple answer as to how to insert multiple rows of raw data into an Access database using an Insert Into SQL statement, this post is one of many to point out that VALUES is for single-row inserts, and you need to use a SELECT to insert more than 1 row - which means your values have to already be in the database.

I don't believe there's a straightforward way to do it with a single SQL statement, but you can use free MyOLEDBExpress database editing tool to accomplish it by cutting/pasting your columns of data from Excel spreadsheet or other source directly into a table. If you are combining data with values already in the database you can use a SELECT clause with the INSERT INTO statement to extract the insert values from tables.

It may be worth the effort if you have hundreds of rows of data or more. Here's the Insert statement after the temp table has been created.

INSERT INTO the_Access_Table (field1, field2) SELECT col1, col2 FROM temp_Table INNER JOIN other_Table ....