Column order does matter so if (and only if) the column orders match you can for example:
insert into items_ver
select * from items where item_id=2;
Or if they don't match you could for example:
insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;
but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read
There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:
insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;
dbfiddle here
I think it's an implementation detail.
A conforming implementation is not required to perform the exact
sequence of actions defined in the General Rules, provided its effect
on SQL-data and schemas, on host parameters and host variable, and on
SQL parameters and SQL variables is identical to the effect of that
sequence. The term effectively is used to emphasize actions whose
effect might be achieved in other ways by an implementation.1
I think an implementer could evaluate a common table expression 20 times, even in 20 different ways, and still have a conforming implementation. The only relevant issue is whether "its effect . . . is identical to the effect" of the sequence of actions defined in the General rules.
[1]. Section 6.3.3.3, "Rule evaluation order", in a draft of the SQL 2008 standard, having the local filename 5CD2-01-Framework-2006-01.pdf, p. 41 I have no idea where I got it. Google might know.
Best Answer
Apparently this is a MySQL extension, the SQL 2011 spec defines the insert grammar as,
You can see this talked about on the Pg Lists