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.
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> 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.
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.