I was asked in a viva "how can you create a table structure without copying data from a database table?" I was quite sure with my answer. My answer was:`
CREATE TABLE new_table AS (SELECT *FROM old_table);
Was I right or wrong?
4 Answers
CREATE TABLE new_table AS (SELECT * FROM old_table where 0=1);
Comments
No. Your answer is incorrect. You can use this SQL query.
CREATE TABLE *new_table* AS (SELECT *FROM *old_table* WHERE *statement=false*);
Like this an example is following:
CREATE TABLE *new_table* AS (SELECT *FROM *old_table* WHERE *1=2*);
I think it will serve your purpose...:P
Comments
CREATE TABLE new_table AS SELECT * FROM old_table where 0=1;
Here in where clause we can use any unequality statement like where 1=2,2=3..etc., which should inform optimizer this 'where' condition will definitely return false, thus preventing any data to be copied from old_table.
Comments
if you are using workbench it has a option in left side of the screen "Data export" just click on it select the db you want to copy structure of and there will be a drop down option select "Dump structure only" and export to a folder.
import this file where ever you want using option data import restore and select path and type new schema name and import. you have new schema with the structure you want.
create table like.CREATE TABLE new_table AS (SELECT *FROM old_table WHERE 1=0);