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.

25 May

SAS: INSERT / VALUES

Originally, I’d planned this post to be about using CASE in an UPDATE.  However, strangely, unexpectedly,  it turned out that the big 3 all used the same syntax.  So, this will just be a quick overview of the differences between SAS and MySQL / SQL Server using INSERT.

Here’s some SAS code that creates a table, inserts some data and then updates it.

proc sql;
CREATE TABLE work.myjunk
(
	name CHAR(1),
	number INT
);
INSERT INTO work.myjunk VALUES ('A',1)
                        VALUES ('B',2)
                        VALUES ('C',3);
UPDATE work.myjunk
SET number =
CASE WHEN name = 'A' then 11
     WHEN name = 'B' then 22
	 ELSE 33
	 END;
SELECT * FROM work.myjunk;
quit;

name    number
--------------
A           11
B           22
C           33

There are two things worth noting in this code. First the INT data type. SAS accepts it, 9 others according to the Study Guide which it will then convert to one of the two SAS data types: number and character. In this case, INT is converted to a number.

I, finally, got smart and created a work database for MySQL and work schema for SQL Server.  It should help my code look a bit more consistent, even if work. means one thing in SAS, another in MySQL and still another in SQL Server.

The other thing, which is what led me to write this post, is that SAS uses a VALUES clause before each row in an INSERT. As you’ll see in the MySQL code (it’s the same with SQL Server) we only use a single VALUES clause.

mysql> CREATE TABLE work.myjunk
    -> (
    ->  name char(1),
    ->  number int
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO work.myjunk VALUES ('A',1),('B',2),('C',3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE work.myjunk
    -> SET number =
    -> CASE WHEN name = 'A' then 11
    ->      WHEN name = 'B' then 22
    ->   ELSE 33
    ->   END;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

Hopefully, this makes sense.  You use a single VALUES clause for SQL Server / MySQL and a VALUES clause for each row with SAS.  And, just be aware that you can use a CASE statement in an UPDATE.

24 May

INSERT with a SET

This is something I did not know you could do, but you can use SET for an INSERT statement.  I guess I’m a noob.  Anyways, here is how it works in the three SQL variants that I write about.

SQL Server

INSERT INTO myjunk1
SET id = 1;

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

I think it’s time I deleted the tables I’ve named junk. Anyways, no surprise that SQL Server doesn’t like it.

MySQL

mysql> INSERT INTO myjunk
    -> SET name = 'E',
    -> number = 6;
Query OK, 1 row affected (0.04 sec)

Hmmm, another junk. Anyways, it works fine here. I’ll give more detail in the next section as I’m really writing this article for SAS.

SAS

proc sql;
CREATE TABLE work.myjunk
(
	id num
);
INSERT INTO myjunk
SET id = 2;
SELECT * 
FROM work.myjunk;
quit;

      id
--------
       2

You can also add multiple values to the table as follows:

proc sql;
CREATE TABLE work.myjunk
(
	id num
);
INSERT INTO myjunk
SET id = 3
SET id = 4
SET id = 5;
SELECT * 
FROM work.myjunk;
quit;

      id
--------
       3
       4
       5

Interestingly, you can recreate a table, albeit this is a temporary table, by running a CREATE TABLE on top of it, which is why there are only 3 values in this table. If you have multiple columns in the table, you treat them exactly like you would an UPDATE statement.

proc sql;
CREATE TABLE work.myjunk
(
	id num,
	id1 num
);
INSERT INTO myjunk
SET id = 3,
   id1 = 13
SET id = 4,
   id1 = 14
SET id = 5,
   id1 = 15;
SELECT * 
FROM work.myjunk;
quit;

      id       id1
------------------
       3        13
       4        14
       5        15

Anyways, that’s pretty much it for INSERT / SET. It’ll probably come up on the SAS Advanced test, assuming I get there, and I have to admit that I’d have blown this one.