sqlms-accessms-access-2010insert-intoansi-sql-92

Cant use insert with subquery

This problem is really weird and I believe related with Access itself.

I`m trying to insert this way (really simplified version, but anyway the error is the same):

INSERT INTO phones(a, b)
select * from ( select C, D from banks) AS BB;

Access returns error, saying that:

Instruction INSERT INTO consist unknown field C

Then I tried another query, that looks the same:

INSERT INTO phones(a, b)
select BB.* from ( select C, D from banks) AS BB;

Error: The number of field isn't matching.

However query without subquery works awesome!

INSERT INTO phones(a, b)
select C, D from banks;

So that is wrong here?

Solution

  • As I can see now, Access cant match fields itself , this why names in the subquery should be the same:

    INSERT INTO phones(a, b)
select * from ( select C as a , D as b from banks) AS BB;

    Wasted more than 1 hour to solve.