26 May

CREATE TABLE AS

Creating a table is, more or less, similar among the three variants that I write about.  You create a table and you roll with it.  However, the CTAS statement (CREATE TABLE AS) is handled somewhat differently amongst them.  So, I’m going to hit that one up.

Really, I’m just writing this up because outside of MySQL I always forget this syntax. Also, when you create a table this way, it won’t include

MySQL

Create a copy of a a table with all of its columns included.

mysql> CREATE TABLE myjunk1 AS SELECT * FROM myjunk;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myjunk1;
+------+--------+
| name | number |
+------+--------+
| A    |     11 |
| B    |     22 |
| C    |     33 |
+------+--------+
3 rows in set (0.00 sec)

Create a copy of a table, however, do not include any of its columns.

mysql> CREATE TABLE myjunk1 AS SELECT * FROM myjunk WHERE 1 = 2;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myjunk1;
Empty set (0.00 sec)

mysql> DESC myjunk1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| name   | char(1) | YES  |     | NULL    |       |
| number | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Also constraints are not included, for example.

mysql> ALTER TABLE myjunk ADD PRIMARY KEY(number);
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE myjunk1 AS SELECT * FROM myjunk;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESC myjunk1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| name   | char(1) | YES  |     | NULL    |       |
| number | int(11) | NO   |     | 0       |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc myjunk;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| name   | char(1) | YES  |     | NULL    |       |
| number | int(11) | NO   | PRI | 0       |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Note that the primary key is not included in myjunk1.

I could go on but this is to help me remember the syntax.  You can read more here:

http://dev.mysql.com/doc/refman/5.6/en/create-table.html

SQL Server

Conceptually, SQL Server works in a similar fashion to MySQL, except, that they have their own syntax for the process. This won’t work:

CREATE TABLE myjunk1 AS SELECT * FROM myjunk;

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Instead you need do something like this:

SELECT *
INTO myjunk1
FROM myjunk
WHERE number > 22;
SELECT * FROM myjunk1;

Name	Number
C	33

I had a really hard time remembering this syntax at first because I was so used to the CTAS variant.

http://msdn.microsoft.com/en-us/library/ms188029.aspx

SAS

SAS allows the CTAS variant, so I won’t repeat that but it also does something else. When you are creating a copy of a table, but don’t want to include any of it’s data, you can do the following:

proc sql;
CREATE TABLE work.myjunk
(
	name char(1),
	number int
);
INSERT INTO work.myjunk VALUES ('A',1)
                        VALUES ('B',2)
                        VALUES ('C',3);
CREATE TABLE work.myjunk1
LIKE work.myjunk;

DESCRIBE TABLE work.myjunk1;
quit;

From the log.

31         DESCRIBE TABLE work.myjunk1;
NOTE: SQL table WORK.MYJUNK1 was created like:

create table WORK.MYJUNK1( bufsize=131072 )
  (
   name char(1),
   number num
  );

Constraints, more or less, appear to follow the same rules as they do in the other variants.

All Variants – Adding A New Column

You can also create a new column simply by adding it to the result set. Be careful about using apostrophes to name the new column as it might not work.

mysql> CREATE TABLE myjunk1 AS
    -> SELECT name, number, 123 AS newCol
    -> FROM myjunk;
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESCRIBE myjunk1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| name   | char(1) | NO   |     | NULL    |       |
| number | int(11) | NO   |     | 0       |       |
| newCol | int(3)  | NO   |     | 0       |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM myjunk1;
+------+--------+--------+
| name | number | newCol |
+------+--------+--------+
| A    |     11 |    123 |
| B    |     22 |    123 |
| C    |     33 |    123 |
+------+--------+--------+
3 rows in set (0.00 sec)

You can also CAST / CONVERT the data type. For example.

mysql> CREATE TABLE myjunk1 AS
    -> SELECT name, number, CAST(123 AS char) AS newCol
    -> FROM myjunk;
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESC myjunk1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| name   | char(1)    | NO   |     | NULL    |       |
| number | int(11)    | NO   |     | 0       |       |
| newCol | varchar(3) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I’m pretty sure that SAS doesn’t work that way but I’m not ready to write that today as I’m still learning that side of the house.  Another day, another post.

Leave a Reply

Your email address will not be published. Required fields are marked *