Copying an entire table to another table
I want to copy an entire table to another table, same platform. Is it either
insert into tablename2 (column names) values (select * from tablename1 where... );
----(or)-----------------------
insert into tablename2 select * from tablename1 where...
The VALUES ([list]) clause is valid only when you are supplying literal values, not a select statement. Assuming the target table has already been defined with a CREATE TABLE statement, then the second of your two queries is correct.
insert into tablename2 select * from tablename1 where...
Note that this "select star" format is permitted only when the tables have identical structures and no reformatting is required. (see How can I use INSERT INTO to copy an entire table into another? 23 May 2001).
Some databases have syntax which allows you to make a copy of a table without pre-defining the target table using CREATE TABLE. In Oracle and Postgresql, I believe it's CREATE TABLE ... AS SELECT ..., and in Microsoft Access and SQL/Server, it's SELECT ... INTO.
select field3 as col1 , ( field7 + field8 ) * 9.37 as col2 , 0 as col3 into tablename2 from tablename1
Note that aliases are supplied to give the columns good names (otherwise they get database-generated names).