SQL Interview Questions | Copy the Table

Question

How can you create an empty table from an existing table? Write the steps and explain the working.

Take the following table for instance and create a new table named as cstudent.

NAME MARKS ROLL NUMBER
Ashutosh 87 1001
Bhavya 92 1002
Garima 69 1003
Pratham 75 1004
Sushant 90 1005
in progress 1
Dhruv2301 1 year 6 Answers 1196 views Great Grand Master 0

Answers ( 6 )

  1. create table cstudent
    AS
    (select * from student
    where 1=0
    )

  2. create table cstudent(select * from student where 1=2);

  3. Copy structure only (copy all the columns)
    Select Top 0 * into cstudent from OldTable

    Copy structure with data
    Select * into cstudent from OldTable

    If you already have a table with same structure and you just want to copy data then use this
    Insert into cstudent Select * from OldTable

  4. Create Table Cstudent
    select *from student
    /* The above code just copies the data and tables ,It doesn’t copy the database objects like Primary key,foreign Key,indexes*/
    /* To copy the one table and also all the dependent Objects use the Following code*/
    Create Table IF NOT EXISTS Cstudent LIKE Student;
    INSERT Cstudent
    SELECT *FROM Student
    /* We need to execute two statements,First statement creates a new table by duplicating the existing table .The second statement Inserts data from the existing table in to the new table*/

  5. PROC SQL; CREATE TABLE
    NEW_TAB
    AS SELECT * FROM
    ORIGINAL_TAB
    WHERE 1=2

Leave an answer

Browse
Browse