INSERT INTO SELECT doesnt work properly

Question

I'm using VBA on ACCESS.I have a weird problem. Here's is the main query

INSERT INTO [Temp2]  ([Prodotto pulizie],Materiale,Compatibility) VALUES 
(SELECT CD.[Prodotto pulizie],CD.Materiale,CD.Risultato 
FROM [Compatibilità_detergenti] as CD 
WHERE CD.[Prodotto pulizie] = 'P1' and CD.Fonte = 'INTERNA' and CD.Materiale = 'ABS;);

It tells me that there is a sintax error but if i try the insert and the select separatley they work , in fact

INSERT INTO [Temp2] ([Prodotto pulizie],Materiale,Compatibility) VALUES ('P1','INTERNA','ABS') works!

SELECT CD.[Prodotto pulizie],CD.Materiale,CD.Risultato 
FROM [Compatibilità_detergenti] as CD 
WHERE CD.[Prodotto pulizie] = 'P1' and CD.Fonte = 'INTERNA' and CD.Materiale = 'ABS;

works! The SELECT returns exactley the record i manually inserted in INSERT. So basically the problem only appears when i merge insert and select, is there any sintax error you can see?

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

I dont know about the vba but as i checked your query you write VALUES. remove this according to mysql try this

INSERT INTO [Temp2]  ([Prodotto pulizie],Materiale,Compatibility)  
SELECT CD.[Prodotto pulizie],CD.Materiale,CD.Risultato 
FROM [Compatibilità_detergenti] as CD 
WHERE CD.[Prodotto pulizie] = 'P1' and CD.Fonte = 'INTERNA' and CD.Materiale = 'ABS';

INSERT INTO...SELECT for all MySQL columns

edited May 23 '17 at 12:04

Community

1
1

answered Jan 13 '16 at 10:24

Vipin Jain

3,686
16
35

Pasted your code but tells me syntax error on the WHERE line – nescionomen93 Jan 13 '16 at 10:32
two changes i have done in your query first remove the `VALUES` and second is single quate `'` in the last. – Vipin Jain Jan 13 '16 at 10:40
for more helo accept my answer so i can do help for other. its point appreciate me for help other – Vipin Jain Jan 13 '16 at 10:43

INSERT INTO SELECT doesnt work properly

1 Answers1