Use Insert Into to insert multiple records

[Kenny A.]

I have a table that gets replaced each month with new data from a feed, no
problem. I need to insert into this this table once the new values are there,
the same records each month. I will not be populating all the fields in the
table with values. I am using the INSERT INTO tablename
(field1,field2,field3,...) VALUES(value1,value2,value3,...) this is only good
for inserting one record. How do I add multiple records in this one insert? I
know I could make up an insert query for each record, but that's not efficent.

Thanks in advance
Kenny A.

 

[louisjohnphillips]

I have a table that gets replaced each month with new data from a feed, no
problem. I need to insert into this this table once the new values are there,
the same records each month. I will not be populating all the fields in the
table with values. I am using the INSERT INTO tablename
(field1,field2,field3,...) VALUES(value1,value2,value3,...) this is only good
for inserting one record. How do I add multiple records in this one insert? I
know I could make up an insert query for each record, but that's not efficent.

Thanks in advance
Kenny A.

Is the answer to your question as trival as this?

insert into tablename ( field1, field2, field3 )
select field1, field2, field3
from SameOldRowsUsedEveryMonth

 

[Jeff Boyce]

Kenny

I'm not tracking ... what do you mean by "make up an insert query for each
record"?

In Access, a record is a row. An insert query would take all of the data
(i.e., all of the records/rows) in one source and append it to another
'table'.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kenny A.]

Jeff and Louis,
I am not feeding the records from another table into this table. I am
looking to add a query to a macro I already have that runs a bunch of other
queries to do some other updating of this table. What I need to do is insert
several records into a table, but the values I am inserting are not coming
from any other table. My table contains 14 fields, but I will only be
pupulating 5 fields with data. The other fields are not really needed, but
they come from a feed that I can not control. My question is, is there a way
for me to insert multiple records into my table using the SQL statement
INSERT INTO tablename(field1,field2, field3,field4,field5)
VALUES(value1,value2,value3,value4,value5)? This insert statement will only
insert one record, but is there a way to include values for each of the other
records I need to insert? Jeff, in response to your question about "make up
an insert query for each record", I can create a query that will insert a
single record, but do this for each of the records I need to insert. This
would require me to write 23 individual queries (one for each record) that I
need to insert to my table. Can I create a single Insert query and have the
VALUES I need to insert in this one query? Is there a way to repeat the
VALUES portion of the statement for each record I need to insert? Remember, I
am not getting these values from another table.

Thanks
Kenny A.

 

[Jeff Boyce]

Kenny

You mention "come from a feed". What is the form/format of that feed? Is
it a text file? Is it an Excel file? Is it an Access table?

Any (OK, most any) common "feed" can be treated like a table, and used as a
source for a single (append) query, rather than treating each "row" of data
as a separate "feed".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kenny A.]

Jeff,
You seem to be fixated on the feed I am getting. Every month I get a feed
to refresh my table. It is missing records that I need to add back in each
month. The feed is not the question. The question is: Is there a way to
insert multiple records via an INSERT INTO sql statement? Is there a way to
include the VALUES portion of the statement to include multiple record values?

Kenny A.

 

[Jeff Boyce]

Kenny

I'm focusing on the source data (your 'feed') because there may not be any
need to insert the data one row at a time, as your earlier posts seemed to
indicate you wanted to do.

If the source data is in a form that Access can treat as a "table", you can
create a SINGLE append query to insert ALL of the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

The question is: Is there a way to

No to the second question; yes to the first.

INSERT INTO targettable (field, field, field)
SELECT field, field, field FROM sourcetable;

John W. Vinson [MVP]

 

[Kenny A.]

Jeff,
The feed is coming to me as an EXCEL file that I Import directly into my
table without using any SQL.
John,
You show a Select from sourcetable, There is no sourcetable to select
from. My initial feed is imported directly from an EXCEL file into my table.
There are a bunch of records that are missing each month and have to be
inserted back into the table along with the new data. Since you mentioned
there is no way to add multiple records via the INSERT INTO statement, I will
work around that.

Thanks to both of you.
Kenny A.

 

[Jeff Boyce]

Kenny

Your 'source' table is your imported Excel data.

If you incoming data is coming from Excel, there's a chance it is not
particularly well-normalized. This would mean you would not get the benefit
of Access' relationally-oriented features and functions.

Still, if you have the data already in a table ("import directly into my
table"), you can first make sure your Access .mdb file has a link to that
table (if it isn't already in the .mdb file), then create a query that
returns the data you want to add to your other table.

Finally, when the (select) query is selecting what you need to add, convert
it to an append query, setting both the "to" table and the "to" fields where
the data will go.

Regards

Jeff Boyce
Microsoft Office/Access MVP