Department Table
| deptno | dname | loc |
|---|---|---|
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
To create a new table with the same set of columns as an existing table, you can use a SELECT statement with the column names specified and a table alias. For example, to create a new table called Department_2 with the same columns as the original Department table, you could use the following statement:
DB2
When using the CREATE TABLE command, use the LIKE clause:
CREATE TABLE Department _2 LIKE Department
Oracle, MySQL, and PostgreSQL
Use the CREATE TABLE command with a subquery that returns no rows:
CREATE TABLE Department _2
AS SELECT *
FROM Department
WHERE 1 = 0
SQL Server
Use the INTO clause with a subquery that returns no rows:
SELECT *
INTO Department _2
FROM Department
WHERE 1 = 0
Discussion
DB2
To create a new table based on the structure of an existing table in DB2, you can use the CREATE TABLE…LIKE command. This command allows you to specify an existing table as the pattern for your new table so that the new table will have the same set of columns and column types as the original table. To use this command, specify the name of the pattern table following the LIKE keyword
Oracle, MySQL, and PostgreSQL
When using the CREATE TABLE AS SELECT (CTAS) statement, the new table that you are creating will be populated with all rows returned by the SELECT query, unless you specify a condition in the WHERE clause that evaluates to false. In the example provided, the expression 1 = 0 in the WHERE clause of the query causes no rows to be returned, so the resulting table will be empty. This is because the condition 1 = 0 will always evaluate as false, so no rows will match the condition and be included in the results. The new table will have the same columns as those specified in the SELECT clause of the query, but it will not contain any data. You can then insert data into the new table as needed.
SQL Server
When using the SELECT INTO statement to create a new table from an existing query, the new table will be populated with all rows returned by the SELECT query, unless you specify a condition in the WHERE clause that evaluates to false. In the example provided, the expression 1 = 0 in the WHERE clause of the query causes no rows to be returned, so the resulting table will be empty. This is because condition 1 = 0 will always evaluate to false, so no rows will match the condition and be included in the results. The new table will have the same columns as those specified in the SELECT clause of the query, but it will not contain any data. You can then insert data into the new table as needed.