Firebird Insert
lazarus / 16-Databases

----------------------------------------------------------------------
TRNelson · 2016-02-20 05:45

The following code generates the error message in the attachment.  I
run the exact same SQL sequence in FlameRobin and it works fine.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLquery1.SQL.Text := 'INSERT INTO NEW_TABLE SELECT * FROM
OLD_TABLE';SQLquery1.ExecSQL;SQLTransaction1.commit;
Am I missing something?

----------------------------------------------------------------------
balazsszekely · 2016-02-20 06:01

Quote@TNNelson
Am I missing something?
Make sure the filed types are the same for both table. For example if
you have a varchar(16) in old table, the same field should be at least
varchar(16) in the new table. This is what the error message is about.

----------------------------------------------------------------------
TRNelson · 2016-02-20 13:28

Quote from: GetMem on February 20, 2016, 07:01:44 amQuote@TNNelson
Am I missing something?
Make sure the filed types are the same for both table. For example if
you have a varchar(16) in old table, the same field should be at least
varchar(16) in the new table. This is what the error message is about.
The fields are identical in each table.  The only difference is the
order of the fields.  I made the order the same and now it works.  I
am quite surprised that the order has to be the same since the column
names in each table is the same.

----------------------------------------------------------------------
rvk · 2016-02-20 14:28

Quote from: TRNelson on February 20, 2016, 02:28:51 pmI am quite
surprised that the order has to be the same since the column names in
each table is the same.
What's because you did a SELECT *. You could have also build the field
list and used that list in the SELECT. Or you could have done INSERT
INTO TABLE (field3, field2, field1) FROM SELECT * etc.

See for an example
http://www.firebirdsql.org/refdocs/langrefupd21-insert.html
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
Dumbbells (firstname, lastname, iq)  SELECT fname, lname, iq FROM
Friends ORDER BY iq ROWS 1  returning id, firstname, iq INTO :id,
:fname, :iq;
So yeah, the insert fieldlist must match the select fieldlist exactly.
There isn't any magical name-matching done.

----------------------------------------------------------------------
TRNelson · 2016-02-20 18:06

Quote from: rvk on February 20, 2016, 03:28:49 pmSo yeah, the insert
fieldlist must match the select fieldlist exactly. There isn't any
magical name-matching done.
I get it, but I am a great fan of magic.