27 May

Inserting with a SELECT

Honestly, I don’t think I’ve ever done this outside of a book, but, it’s something that comes up so it’s worth mentioning.  Fortunately, everyone happily works the same way so I’m just going to do this in MySQL.

Suppose we have two identical tables and we want to copy data from one to another. We could use an external tool, like BCP, or mysqldump, or we could just use a INSERT / SELECT as follows;

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

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

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

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

mysql> INSERT INTO myjunk1
-> SELECT * FROM myjunk;
Query OK, 3 rows affected (0.02 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)

This, thankfully, is super clean and for the most part it just works. However, there are a couple of tweaks to it.

mysql> INSERT INTO myjunk1
    -> SELECT number, name FROM myjunk;
ERROR 1406 (22001): Data too long for column 'name' at row 1

This was caused because the Name column is a char(1) so it can’t handle the longer number types.

mysql> ALTER TABLE myjunk1 MODIFY name varchar(10);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO myjunk1
    -> SELECT number, name FROM myjunk;
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'number' at row 1

It would have been nice if it had dropped this error first, before I modified the column, but MySQL isn’t know for solid/clean errors. Anyways, just make sure that if you are switching columns around, for some reason, that you don’t bonk the data types.

Also, you must have an equal number of columns. For example, this will not work.

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

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

mysql> INSERT INTO myjunk1
    -> SELECT * FROM myjunk;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

This, correctly, tossed an error in SQL Server, SAS and MySQL.

Good luck, and hope it helps someone, someday.

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.

10 Apr

SAS: Select Groups

This is very similar to a CASE statement in SQL, except, this is SAS, so, of course, it’s different.  Lets just roll right into an example.

data mydata;
	infile '....';
	input region $ 1-12 quarter 13 sales comma9.;
	length myVar $ 6;
	select (quarter);
		when (1) myVar='First';
		when (2) myVar='Second';
		when (3) myVar='Third';
		when (4) myVar='Fourth';
	end;
run;

ods listing;

options nodate nonumber;

proc print data=mydata label;
run;

Obs     region      quarter     sales    myVar
  1    NorthEast       1       158510    First 
  2    SouthWest       4       292801    Fourth
  3    NorthEast       3       172301    Third 
  4    SouthWest       1       384220    First 
  5    NorthEast       2       506510    Second
  6    SouthWest       3       392202    Third 
  7    NorthEast       4       148510    Fourth
  8    SouthEast       1       284720    First 
  9    NorthWest       2       348803    Second
 10    SouthEast       3       122220    Third 
 11    NorthWest       4       384840    Fourth
 12    SouthEast       1       339880    First

As you can see it’s almost exactly like a CASE, except the formatting is a bit weird.  Also, notice the LENGTH statement so all the values would fit.

You can use the OTHERWISE statement to finish to assign missing values.  For instance:

select (quarter); 
when (1) myVar='First'; 
when (2) myVar='Second'; 
otherwise myVar = 'Other';
end;

This will assign any values other than 1 or 2 as Other.

I tried to select a range but that failed, however, apparently you can do math in the conditional test.

data mydata;
	infile '....';
	input region $ 1-12 quarter 13 sales comma9.;
	length myVar $ 6;
	select (quarter); 
		when (3-2) myVar = 'First';
		otherwise myVar = 'Second';
	end;
run;

ods listing;

options nodate nonumber;

proc print data=mydata label;
run;
Obs     region      quarter     sales    myVar
  1    NorthEast       1       158510    First 
  2    SouthWest       4       292801    Second
  3    NorthEast       3       172301    Second
  4    SouthWest       1       384220    First 
  5    NorthEast       2       506510    Second
  6    SouthWest       3       392202    Second
  7    NorthEast       4       148510    Second
  8    SouthEast       1       284720    First 
  9    NorthWest       2       348803    Second
 10    SouthEast       3       122220    Second
 11    NorthWest       4       384840    Second
 12    SouthEast       1       339880    First

I tried division, multiplication, addition and subtraction in the condition test. They all worked. At this point I just assume that anything goes with SAS.

You can also write the above select as follows:

select;
    when (quarter=1) myVar='First';
    when (quarter=2) myVar='Second';
    when (quarter=3) myVar='Third';
    otherwise myVar='Fourth';
end;

Obs     region      quarter     sales    myVar

  1    NorthEast       1       158510    First 
  2    SouthWest       4       292801    Fourth
  3    NorthEast       3       172301    Third 
  4    SouthWest       1       384220    First 
  5    NorthEast       2       506510    Second
  6    SouthWest       3       392202    Third 
  7    NorthEast       4       148510    Fourth
  8    SouthEast       1       284720    First 
  9    NorthWest       2       348803    Second
 10    SouthEast       3       122220    Third 
 11    NorthWest       4       384840    Fourth
 12    SouthEast       1       339880    First
You can even mix and match conditions like this:
select;
    when (quarter=1 or quarter=2) myVar='1st Half';
    when (quarter in (3,4)) myVar='2nd Half';
end;

And, because the fun just keeps on coming. If you set up a SELECT, and don’t include all the values, and don’t use an OTHERWISE clause, you’ll get an error.

ERROR: Unsatisfied WHEN clause and no OTHERWISE clause at line 22 column 1.
It needed a picture.

This post needed a picture.

Here are the basic rules, from what I can see:

  • You must use parenthesis around the condition.  Character searches are embedded in quotes.
  • You can search for multiple values if you separate them by a comma.
  • You can search for multiple conditions.
  • You can not search by a range, however, you can add, subtract, divide and multiply from what I cant tell.  I would assume you could also use string manipulation here but I did not test that.
  • The OTHERWISE statement sets the values for everything else.
  • Each statement is terminated by a semicolon.
  • And you must use the end; statement to terminate the select sequence.

I think this is it.  Sometimes I look at SAS and, man, they just let anything go, at any time.  Here’s a link on the SELECT statement.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201966.htm