trouble with insert

#1

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

#2

sheldonlg schrieb:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

Just remove surrounding values() clause from your select.
The syntax for insert ... select is documented here:
http://download.oracle.com/docs/cd/B...4.htm#i2111652
http://download.oracle.com/docs/cd/B...4.htm#i2145081
(Inserting values with a subquery example)

Best regards

Maxim

#3

sheldonlg schrieb:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

Just remove surrounding values() clause from your select.
The syntax for insert ... select is documented here:
http://download.oracle.com/docs/cd/B...4.htm#i2111652
http://download.oracle.com/docs/cd/B...4.htm#i2145081
(Inserting values with a subquery example)

Best regards

Maxim

#4

sheldonlg schrieb:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

Just remove surrounding values() clause from your select.
The syntax for insert ... select is documented here:
http://download.oracle.com/docs/cd/B...4.htm#i2111652
http://download.oracle.com/docs/cd/B...4.htm#i2145081
(Inserting values with a subquery example)

Best regards

Maxim

#5

sheldonlg schrieb:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

Just remove surrounding values() clause from your select.
The syntax for insert ... select is documented here:
http://download.oracle.com/docs/cd/B...4.htm#i2111652
http://download.oracle.com/docs/cd/B...4.htm#i2145081
(Inserting values with a subquery example)

Best regards

Maxim

#6

sheldonlg wrote:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

After posting this, I think I understand the error message. The select
is retrieving multiple rows. It might be that it thinks that only
enough values are going to be supplied and so not enough fields are
being specified. If this is the case, then how do accomplish putting in
N rows of data obtained from the select where each row has values for
each of the named fields?

#7

sheldonlg wrote:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

After posting this, I think I understand the error message. The select
is retrieving multiple rows. It might be that it thinks that only
enough values are going to be supplied and so not enough fields are
being specified. If this is the case, then how do accomplish putting in
N rows of data obtained from the select where each row has values for
each of the named fields?

#8

sheldonlg wrote:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

After posting this, I think I understand the error message. The select
is retrieving multiple rows. It might be that it thinks that only
enough values are going to be supplied and so not enough fields are
being specified. If this is the case, then how do accomplish putting in
N rows of data obtained from the select where each row has values for
each of the named fields?

#9

sheldonlg wrote:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

After posting this, I think I understand the error message. The select
is retrieving multiple rows. It might be that it thinks that only
enough values are going to be supplied and so not enough fields are
being specified. If this is the case, then how do accomplish putting in
N rows of data obtained from the select where each row has values for
each of the named fields?

#10

sheldonlg wrote:

Quote:

This should be so simple, yet it is not working. I am trying to do an
insert into a table where the values inserted are gathered from a select
from another table. This form works:

insert into table2 (select fld1, fld2, fld3 from table1 where blah)

However, I do not want to be dependent upon the order of the fields in
table2 so, I tried this:

insert into table2 (fld1, fld2, fld3) values(
select fld1, fld2, fld3 from table1 where blah)
)

This gave me a not enough values error coming from the first set of
parentheses. I don't understand that error. It should not care how
many fields are inserted, so long as the two sets match. Also, in this
case, all the fields of table2 are named.

Duh! I found the answer. I cannot specify "VALUES". I should have
written:

insert into table2 (fld1, fld2, fld3)
(select fld1, fld2, fld3 from table1 where blah)

Sorry to bother everyone.