Sometimes we need to create a table from a specified pl/sql code or other table. Say we only want to create the table columns and not copy the data. Here is the sql code to create a table from another table along with data.
You can substitute red part of the query to any sql query. This will create table with all the data in it. Now, I want to create the same table but without copying the data. It's actually very simple, you only have to add a where clause to restrict the row number to zero. This is how you can write it:
This could be useful when you have to create a table from different table columns. This automatically adapts datatypes of columns.
Create table table_name as Select * from table_a
You can substitute red part of the query to any sql query. This will create table with all the data in it. Now, I want to create the same table but without copying the data. It's actually very simple, you only have to add a where clause to restrict the row number to zero. This is how you can write it:
Create table table_name as Select * from table_a where 1=0
This could be useful when you have to create a table from different table columns. This automatically adapts datatypes of columns.
Guess how easy it's in SQL Server ?
ReplyDeleteSELECT TOP 0 *
ReplyDeleteINTO new_table_name
FROM original_table_name