SQL - Create table based on another table

Home | Tech FAQ | Interview Questions | Placement Papers | Tech Articles | Learn | Freelance Projects | Online Testing | Geeks Talk | Job Postings | Knowledge Base | Site Search | Add/Ask Question

GeekInterview.com > Tech FAQs > SQL

Go To First | Previous Question | Next Question

SQL | Question 186 of 190

Print

Create table based on another table

How to create table emp1 based on emp, except comm column?

Total Answers and Comments: 5

Last Update: September 26, 2008 Asked by: vijaychakaravarthy

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

September 15, 2008 01:51:38

#1

Member Since: August 2008 Contribution: 4

RE: Create table based on another table

It can be done by create / insert statement as follows ;

   If 'EMP' is the table with structure

   ENO          VarChar(12)
   DoJ           date
   ESAL         Numerix
   COMM       numeric

   Now , we can create EMP1 table using EMP Table and exclude the field 'COMM' as

    create table EMP1 as select ENO, DoJ, ESAL from EMP where 1<0 ( this false condition will copy the structure only , not rows from EMP table)

Is this answer useful? Yes | No

September 16, 2008 09:12:24

#2

vishnu vardhan reddy

Member Since: September 2008 Contribution: 6

RE: Create table based on another table

create table tablename as select * from emp where 1=2
only table structure will be copied

Is this answer useful? Yes | No

September 16, 2008 09:19:05

#3

vishnu vardhan reddy

Member Since: September 2008 Contribution: 6

RE: 2nd highest salary in a table

select e.* from(select ename,sal, dense_rank() over(order by sal desc) ranksal from emp)e where e.ranksal=2

Is this answer useful? Yes | No

September 25, 2008 06:52:29

#4

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Create table based on another table

create table_name
as select coulmn_name from table_name;

Is this answer useful? Yes | No

September 26, 2008 01:39:30

#5

jandhyala.aparna

Member Since: September 2008 Contribution: 1

RE: Create table based on another table

CREATE TABLE emp1 AS (SELECT empno, ename, hiredate, sal
                                    FROM emp);

GENERAL SYSTAX :

CREATE TABLE <table name> AS (SELECT <column list that you wish to include>
                                                     FROM <existing table name>
                                                     [WHERE] <condition> );
where clause is optional.

Is this answer useful? Yes | No

Sponsored Links

About Us | Privacy Policy | Terms and Conditions | Contact | Site Map | Add Question | Propose Category | RSS Feeds | Articles Sitemap | Site Updates | Add Resource

Copyright © 2005 - 2008 GeekInterview.com. All Rights Reserved