What is the point of creating table with no data in SQL?

Question

I'm looking at somebody else's code and they've got about 200 lines where they use CREATE TABLE with a SELECT STATEMENT, just to finish it with the statement WITH NO DATA And then directly underneath, the same queries are used with an INSERT statement. The final table have over 400k rows. I just wanted to know why this would be seen/if this is best practice? And if it isn't best practice, what would be a better way to do it?

Answer 1

You don't include the code, but from your description you have:

create table mytable as (select <...>) WITH NO DATA;
insert into mytable (select <...>);

Where the select <...> are exactly the same.

That being the case, no this is not a best practice nor even a good idea as the select <...> has to be evaluated twice. Granted the time required is likely trivial compared to the time required to load the table.

Answer 2

It is a general trick to create a table with a template of a dataset generated by a query. In this case the table is created not only with columns and exactly the same datatype for every columns, but also with all of the column constraints NOT NULL and CHECK if any, but without the table constraints (PK, FK, unique....). This avoid to write a complex CREATE TABLE and synchronize the structure of the persistant tables in the database to the new table generated by this way, that are often temporary table or "kleenex" tables like toses used for stagging data... Don't forget that as database is not a fixed structure but can evolve at anytime, peculiarly with some ALTER TABLE... (adding columns or new constraints).