Forum Discussion
INSERT Multiuple Rows Not Working
I have a query (executed from a macro) as follows:
INSERT INTO PatronsWork (PatronID)
SELECT DISTINCT EventAttendance.PatronID
FROM EventAttendance;
I get nothing inserted. If I remove the first line in the query and switch to Datasheet view (executing the SELECT statement only), the results are exactly as expected. So, the SELECT part is returning a non-empty set of values but nothing is inserted. PatronsWork is an existing table having a PatronID column. Can anyone show me my mistake here? Many thanks if so.
Here is a list of reasons queries are non-updateable. Yours is on the list.
- CREATE TABLE EventAttendance (`PatronID` long);
INSERT INTO `EventAttendance` VALUES ('12');
INSERT INTO `EventAttendance` VALUES ('12');
select * FROM EventAttendance;
CREATE TABLE PatronsWork (`PatronID` long);
INSERT INTO PatronsWork (PatronID)
SELECT DISTINCT EventAttendance.PatronID
FROM EventAttendance;
select * from PatronsWork;
On problem on myside.
online test
http://anyoupin.cn/bsbm/stu60/sqlDao/sqlEditor_dao.html
select * FROM EventAttendance;
PatronID
12
12
INSERT INTO PatronsWork (PatronID)SELECT DISTINCT EventAttendance.PatronID FROM EventAttendance;
select * from PatronsWork;
PatronID
12 - modify PatronsWork table and add a Unique index (no duplicate) on PatronID field. Then you can simply use a simple select query to add to the table:
Insert Into PatronsWork (PatronID) Select PatronID From EventAttendance;- Thanks to all who responded. The SQL I posted is actually a fairly minimal reduction of what I'm really trying to do (which involves multiple subqueries, joins, temp variables and more). The example posted was paired down to a minimum statement to illustrate the problem. I was hoping to avoid having to use VBA (which I'm not very experienced in), but maybe that's the only workable solution.
By the way, I also go no joy from the following:
SELECT EventAttendance.PatronID
INTO BrandNewTable
FROM EventAttendance;
I've never used a SELECT.INTO statement before, but it looks simple enough. However, I get no new table produced in executing the above. Could this be related?please provide some DDL(Data Definition Language) or some data and your expected result.
Both sqls you metioned all work well on my side.
- I would guess that the insert violates validation requirements of the destination table and you have warnings turned off. Therefore, you are not seeing any warning messages.
- Once again, thanks for the responses.
I have discovered that the following SQL works just fine if executed via RunSQL in a macro:
INSERT INTO PatronsWork (PatronID) SELECT DISTINCT EventAttendance.PatronID FROM EventAttendance
The exact same SQL does nothing (and produces no diagnostic) when executed in the query (executed via an OpenQuery in a macro). The same is true of a SELECT.INTO statement, by the way. So, the problem would seem to be associated with the query. The SQL, table properties, etc. all seem fine if used with RunSQL.
I cannot see any query properties that would suspect, but maybe there's something there that should be examined?
I'm not sure if I could just switch to RunSQL as the actual statement is a bit longer and would be hard to understand if all on one line (and may be longer than Access would even allow - haven't tried that yet but will shortly).- SOLVED ... well not really, but now working.
I tried a Hail Mary: I deleted the query containing the problem SQL and created a new one with the same name and containing the same SQL. It worked this time. I have no idea what the earlier problem was (and almost surely never will).
Thanks to all.
