Create SQL table with the data from another table

Question

How do I create a table using data which is already present in another table (copy of table)?

Answer 1

If you are using MySQL, you may want to use the CREATE TABLE ... AS SELECT syntax to create a table defined by the columns and data types of another result set:

CREATE TABLE new_table AS
SELECT * 
FROM   old_table;

Example:

CREATE TABLE old_table (id int, value int);

INSERT INTO old_table VALUES (1, 100), (2, 200), (3, 300), (4, 400);

CREATE TABLE new_table AS
SELECT * 
FROM   old_table;


SELECT * FROM new_table;
+------+-------+
| id   | value |
+------+-------+
|    1 |   100 |
|    2 |   200 |
|    3 |   300 |
|    4 |   400 |
+------+-------+
4 rows in set (0.00 sec)


DESCRIBE new_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

For other DBMSes, that do not support this syntax, you may want to check out @OMG Ponies' answer for a more portable solution.

Answer 2

For SQL Server

SELECT * 
INTO NewTable
FROM OldTable

Answer 3

The most portable means of copying a table is to:

1. Create the new table with a CREATE TABLE statement

2. Use INSERT based on a SELECT from the old table:

   INSERT INTO new_table
   SELECT * FROM old_table
   

In SQL Server, I'd use the INTO syntax:

SELECT *
    INTO new_table
  FROM old_table

...because in SQL Server, the INTO clause creates a table that doesn't already exist.