Use Insert Into to insert multiple records

Use Insert Into to insert multiple records

Post by S2VubnkgQS » Thu, 26 Jul 2007 06:16:03


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.
 
 
 

Use Insert Into to insert multiple records

Post by louisjohnp » Thu, 26 Jul 2007 07:14:07


Is the answer to your question as trival as this?

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

 
 
 

Use Insert Into to insert multiple records

Post by Jeff Boyc » Thu, 26 Jul 2007 07:22:22

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
 
 
 

Use Insert Into to insert multiple records

Post by S2VubnkgQS » Thu, 26 Jul 2007 22:06:04

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.
 
 
 

Use Insert Into to insert multiple records

Post by Jeff Boyc » Fri, 27 Jul 2007 08:58:00

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
 
 
 

Use Insert Into to insert multiple records

Post by S2VubnkgQS » Fri, 27 Jul 2007 22:02:03

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" wrote:

 
 
 

Use Insert Into to insert multiple records

Post by Jeff Boyc » Sat, 28 Jul 2007 09:00:07

enny

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

"Kenny A." < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Use Insert Into to insert multiple records

Post by John W. Vi » Sat, 28 Jul 2007 13:57:39

On Thu, 26 Jul 2007 17:00:07 -0700, "Jeff Boyce" < XXXX@XXXXX.COM >



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]
 
 
 

Use Insert Into to insert multiple records

Post by S2VubnkgQS » Tue, 31 Jul 2007 21:40:05

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.
 
 
 

Use Insert Into to insert multiple records

Post by Jeff Boyc » Tue, 31 Jul 2007 23:03:31

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