INSERT INTO using WHERE??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to use the following command with no luck...

INSERT INTO trasharchive ( archiveprimary, archivedata ) SELECT trashnew.newprimary, trashnew.newdata FROM trashnew WHERE (archiveprimary="Norm")

They key part that I am having trouble with is the WHERE portion. Whenever I run this it always pops up a dialog asking me to enter the value for archiveprimary

Forgive me for the ridiculous names, I'm using it for testing purposes

Thanks for the help.
 
INSERT INTO trasharchive ( archiveprimary, archivedata )
Click to expand...
SELECT trashnew.newprimary, trashnew.newdata FROM trashnew
WHERE (archiveprimary="Norm");

The way Access tries to interpret this is -

INSERT INTO table1
(table1.field1, table1.field2)
{
SELECT table2.field1, table2.field2
FROM table2
WHERE (table2.field = "Norm")
}
;
I am having trouble with is the WHERE portion. Whenever
Click to expand...
I run this it always pops up a dialog asking me to enter
the value for archiveprimary.

Now the WHERE portion applies to the trashnew table. Seems
to be appropriate, the WHERE clause has no reason to apply
to table1 because you are inserting new records and while
doing that we have nothing to do with already existing
records in table1. (Unless of course - if we are violating
validation rules or referential integrity in table1 by
inserting the new records)

The WHERE clauses in your query uses a field name from the
table into which you are trying to insert records. Since
Acecss is unable to locate that field in table2 - it
assumes that this might be a parameter - whose value must
be provided at runtime. Hence the prompt for the value.

Hope this helps

Charanjeev Singh
Microsoft Developer Support Professional
 
Back
Top