Peter Chan
asked on
Problem with 2nd insert line
Hi,
In Access 2013, I put these
insert into ite_tab (name,ite_name) values('Ite 1','');
insert into ite_tab (name,ite_name) values('Ite 2','');
in the query. But I get the error shown in the figure
t605.png
In Access 2013, I put these
insert into ite_tab (name,ite_name) values('Ite 1','');
insert into ite_tab (name,ite_name) values('Ite 2','');
in the query. But I get the error shown in the figure
t605.png
You'll have to execute each INSERT individually (two strings, two commands) instead of both as one string.
With Access SQL you can't combine two INSERT statements. You could run each of them separately.
But if you need to do it with a single statement, you will need to use a more complex query like below
INSERT INTO ite_tab
SELECT 'Ite 1',''
FROM Dual
UNION ALL
SELECT 'Ite 2',''
FROM Dual;
But if you need to do it with a single statement, you will need to use a more complex query like below
INSERT INTO ite_tab
SELECT 'Ite 1',''
FROM Dual
UNION ALL
SELECT 'Ite 2',''
FROM Dual;
ASKER
Sorry Pratima, I get
"Syntax error in FROM clause" due to the following
insert into ite_tab (name,ite_name) select 'Ite 1','' FROm dual
union all select 'Ite 2','' FROm dual
"Syntax error in FROM clause" due to the following
insert into ite_tab (name,ite_name) select 'Ite 1','' FROm dual
union all select 'Ite 2','' FROm dual
Create 2 queries - one for each insert statement as this would be clearer. You also are using the field name which is a reserved word in MS Access so surround it with []. Lastly, no need to insert an empty string into ite_name. Leave it out,
Query1 SQL
insert into ite_tab ([name]) values('Ite 1');
Query2 SQL
insert into ite_tab ([name]) values('Ite 2');
If you must use just one query then:
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;
Regards,
Bill
Query1 SQL
insert into ite_tab ([name]) values('Ite 1');
Query2 SQL
insert into ite_tab ([name]) values('Ite 2');
If you must use just one query then:
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;
Regards,
Bill
ASKER
Sorry, I still get the same error - "Syntax error in FROM clause", due to this
insert into ite_tab ([name],[ite_name]) select 'Ite 1' as [name],'' as [ite_name] from ite_tab
union all select 'Ite 2' as [name],'' as [ite_name] from ite_tab
insert into ite_tab ([name],[ite_name]) select 'Ite 1' as [name],'' as [ite_name] from ite_tab
union all select 'Ite 2' as [name],'' as [ite_name] from ite_tab
Your syntax is still incorrect. Try to duplicate it exactly as shown below and above.
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;
Regards,
Bill
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;
Regards,
Bill
ASKER
But I want to insert into 2 columns in the table.
Hi,
Did you try the code?
Inserting empty data into a column happens automatically when you insert the record. It is like not inserting anything at all into the column and is not necessary. In any case if you want to insert blank data into a second column change the SQL statement to:
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name], NULL as [ite_name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name], NULL as [ite_name] from ite_tab;
This will give the same result as the prior code.
Regards,
Bill
Did you try the code?
Inserting empty data into a column happens automatically when you insert the record. It is like not inserting anything at all into the column and is not necessary. In any case if you want to insert blank data into a second column change the SQL statement to:
INSERT INTO ite_tab
SELECT 'Ite 1' as [Name], NULL as [ite_name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name], NULL as [ite_name] from ite_tab;
This will give the same result as the prior code.
Regards,
Bill
ASKER
Many thanks all.
Sorry, I still get the same error due to this
Sorry, I still get the same error due to this
insert into ite_tab (fld_name,ite_name) select 'Ite 1' as [fld_name],NULL as [ite_name] from ite_tab
union all select 'Ite 2' as [fld_name],NULL as [ite_name] from ite_tab
after having adjusted the table column name from name to fld_name.
Hi,
I was wrong.
You cannot combine INSERT and UNION queries in the same SQL statement. Qlemo is correct above.
This is by definition.
You will need to run 2 queries per my original answer above. You could also create a VBA or macro to do the work.
Regards,
Bill
I was wrong.
You cannot combine INSERT and UNION queries in the same SQL statement. Qlemo is correct above.
This is by definition.
You will need to run 2 queries per my original answer above. You could also create a VBA or macro to do the work.
Regards,
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Qlemo,
That does not work in MS Access. It works is SQL Server but the query generator in MS Access causes a parameter failure.
Regards,
Bill
That does not work in MS Access. It works is SQL Server but the query generator in MS Access causes a parameter failure.
Regards,
Bill
ASKER
Thanks a lot to all.
Bill,
do you have more details to use VBA to do the same thing?
Bill,
do you have more details to use VBA to do the same thing?
Hi,
Sure. Open any module or create a new one. Enter the following:
Public Sub RunMyQueries()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.SetWarnings True
End Sub
This will create a public subroutine that can be called with a button on a form or in a macro, etc.
It turns off the warning messages, runs the queries and turns the warnings back on.
You could also just add the 4 lines to the OnClick event on a form.
Regards,
Bill
Sure. Open any module or create a new one. Enter the following:
Public Sub RunMyQueries()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.SetWarnings True
End Sub
This will create a public subroutine that can be called with a button on a form or in a macro, etc.
It turns off the warning messages, runs the queries and turns the warnings back on.
You could also just add the 4 lines to the OnClick event on a form.
Regards,
Bill
ASKER
Sorry, I've got
run-time error '7873'
when trying to run this
run-time error '7873'
when trying to run this
Option Compare Database
Public Sub RunMyQueries()
DoCmd.SetWarnings False
DoCmd.OpenQuery "insert into ite_tab (fld_name,ite_name) select 'Ite 1' as [fld_name],NULL as [ite_name] from ite_tab"
DoCmd.SetWarnings True
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.