Q
Problem solve Get help with specific problems with your technologies, process and projects.

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).


This was last published in March 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close