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
SQL
4 years
6 Answers
2278 views
Great Grand Master 0
Answers ( 6 )
create table cstudent
AS
(select * from student
where 1=0
)
Correct !!
create table cstudent(select * from student where 1=2);
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
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*/
PROC SQL; CREATE TABLE
NEW_TAB
AS SELECT * FROM
ORIGINAL_TAB
WHERE 1=2