Search code examples
sqlinsertdb2create-table

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

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?

Solution

  • 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.