TIL: Duplicate SQL Table Schema

May 11, 2023

Following some advice I got over on Hacker News I am going to start posting little Today I Learned posts as a way to help me ease into more consistent blog writing.

The Code

-- Note, I run this on Oracle DB
CREATE TABLE NEW_TABLE AS
SELECT *
    FROM OLD_TABLE
    WHERE 1 = 0
;

What this achieves and why

This will create a new table called NEW_TABLE that has the exact same schema as OLD_TABLE. (Note, I don't know if EVERYTHING is the same all the way down, but the schema is the same which is what I care about.)

I generally use this when I have external data coming from a spreadsheet, or an SQL script and I want to insert it into my OLD_TABLE, but first I need to perform some alterations to the data.

Before now I tended to copy the DDL from the source table and run it. This is much quicker!

Lenny's Quest