0

I used ODBC to create a sheet in Excel and add a row to it.

Literally the commands were just:

create table 'update5' ('age' NUMBER);
insert into 'update5'.'age' values (1);

This works and I can see the rows in the sheet and via DBVisualiser and my ODBC query results.

Later, I wrote more SQL to add another row like so:

insert into 'update5' ('age') values (2);

but I get the error:

[Microsoft][ODBC Excel Driver] Cannot expand named range.

I do not know why named ranges are being used, is there a way I can set ODBC to not use them?

Stefan
  • 3,669
  • 2
  • 32
  • 43
  • I'm confused - where were you entering these commands? `INSERT INTO` is not for returning data, it's for adding records to an existing table. `SELECT` would be what you use to view data. There's something being miscommunicated, could you clarify a bit more, and maybe add a screenshot or something more descriptive? – ashleedawg Jul 06 '18 at 10:53
  • Argh - stupid multiple windows open!!! The title is incorrect for this question, I will edit it in a second. I am entering the commands in a script which is being run via the ODBC engine. – Stefan Jul 06 '18 at 11:11

2 Answers2

0

Without knowing more about what your doing, what you're working with, and your end-goal I can't give a definite answer - however, if you're saying this works fine as-is:

create table 'update5' ('age' NUMBER);
insert into 'update5'.'age' values (1);

...then it stands to reason that this:

insert into update5 values (2);

...will not work because your missing:

  • quotation marks (which may or may not be optional in your environment), and,
  • the field name to which you want to export.

In the first insert into statement you have:

'update5'     <-- the destination table
.'age'        <-- the destination field
 values (1);  <-- the value to insert

...so if you're just trying to add a record with the number 2 to the same field, use the same code:

insert into 'update5'.'age' values (2);

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Sorry, the question contains an error. The actual SQL being generated has the relevant formatting. I have edited the question accordingly – Stefan Jul 06 '18 at 11:17
0

Found it.

When you create an table in Excel via ODBC you create a named range of the same name within that table.

When you try to insert like this:

insert into 'update5'.'age' values (2);

It is interpreted as you trying to add to the the named range called update5 which is inside the table update5.

You need to use the syntax:

insert into [update5$].'age' values (2);

to add values to the table called update5.

Stefan
  • 3,669
  • 2
  • 32
  • 43