{"id":94465,"date":"2022-05-23T18:29:35","date_gmt":"2022-05-23T18:29:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94465"},"modified":"2022-05-23T18:29:35","modified_gmt":"2022-05-23T18:29:35","slug":"values-clause-building-tables-out-nothing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/theory-and-design\/values-clause-building-tables-out-nothing\/","title":{"rendered":"The VALUES clause or building tables out of nothing"},"content":{"rendered":"

The VALUES<\/code> clause is probably one of the most misused features in SQL. If you look at SQL forums online, you\u2019ll see people use it as the second clause in an insertion statement, but they only use it to construct a single row at a time, thus:<\/p>\n

BEGIN\r\nINSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)\r\n VALUES ('Aries', '2022-03-21', '2022-04-19');\r\nINSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)\r\n VALUES ('Taurus', '2022-04-20', '2022-05-20');\r\n\u2026 \r\nINSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)\r\n VALUES ('Pisces', '2023-02-19', '2023-03-20');\r\nEND; <\/pre>\n
Each insertion statement ends with a semi-colon, so they will be executed separately and in the order presented. An optimizer doesn\u2019t dare combine them because there might be a forward reference to previous insertions.<\/p>\n
I think people write this kind of code because that this is how you would read punch cards. Each card goes into a card reader, gets buffered, and written in the order presented to the magnetic tape or disk file. Welcome to 1960! Stop mimicking old programming languages like FORTRAN or BASIC that had WRITE statements and put one record at a time into a file. <\/em>Start thinking of working with entire sets.<\/p>\n
Using VALUES to insert rows<\/h2>\n
The VALUES<\/code> clause is more appropriately called a table<\/em> constructor<\/em>. Each row constructor within the table is a comma-separated list enclosed in parentheses. Officially, there is an optional keyword ROW<\/code>. They can be placed at the start of each list. Nobody does this, and it is a bit redundant, but was required in MySQL.<\/p>\n
One of the worst ways of constructing a table is to use the CREATE<\/code> or DECLARE<\/code> construct to build a temporary table, load it with insertion statements, and finally insert the table into the desired destination. This leads to multiple statements with no way to really optimize the insertion and shows that you are really not thinking in sets yet.<\/p>\n
The entire zodiac can be inserted with a single statement like this:<\/p>\n
INSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)\r\nVALUES (\r\n ('Aries', '2022-03-21', '2022-04-19'),\r\n ('Taurus', '2022-04-20', '2022-05-20'),\r\n ('Gemini', '2022-05-21', '2022-06-21'),\r\n ('Cancer', 2022-06- 22', '2022-07-22'),\r\n ('Leo', '2022-07-23', '2022-08-22'),\r\n ('Virgo', '2022-08-23', '2022-09-22'),\r\n ('Libra', '2022-09-23', '2022-10-23'),\r\n ('Scorpius', '2022-10-24', '2022-11-21'),\r\n ('Sagittarius', '2022-11-22', '2022-12-21'),\r\n ('Capricorn', '2022-12-22', '2023-01-19'),\r\n ('Aquarius', '2023-01-20', '2023-02-18'),\r\n ('Pisces', '2023-02-19', '2023-03-20')\r\n);<\/pre>\n
Given a whole set of rows, the optimizer can deal with a single atomic statement. Not only does it save execution time as compared to the row-at-a-time model of insertion, but it presents the optimizer with an opportunity to improve things. The insertion statement can rearrange the list of new rows and pick an optimal ordering. It also means that if one of my rows had an error in it, I wouldn\u2019t have to back out all of the other rows. If I wanted a proper ACID transaction model, I would\u2019ve had to back out each individual insert up until I came to the insertion that gave me the error.<\/p>\n
Here\u2019s the basic syntax. Please note that besides including an expression of the proper data type, you can use the keywords DEFAULT<\/code> or NULL<\/code> in a row constructor. Obviously, those values must make sense in relation to the declaration of the table into which you are inserting.<\/p>\n
VALUES (<row value expression list>) [ , ...n ]\r\n <row value expression list> ::= {<row value expression> } [ , ...n ]\r\n <row value expression> ::= { DEFAULT | NULL | <expression> }<\/pre>\n
Please remember that an expression is not always a simple constant. In fact, it\u2019s very handy to use the CAST (<exp> AS <data type>)<\/code> function as a way to assure that a column in the constructed virtual table has a known data type:<\/p>\n
VALUES (CAST(\u2018foobar\u2019, AS NVARCHAR(10), \r\n      CAST(42 AS INTEGER), CAST(3.14159 AS REAL))<\/pre>\n
The AS<\/code> keyword can also be used, to give the constructed tables each a name. Here is skeleton:<\/p>\n
SELECT X.a, X.b, X.c, Y.a, Y.b, Y.c\r\n FROM\r\n(VALUES (1,2,3) , (4,5,6) ) AS X(a, b, c) ,\r\n(VALUES (1,2,3), (4,5,6) ) AS Y(a,b,c)\r\nWHERE X.a =Y.a\r\n AND X.b = Y.b\r\n AND X.c <= 0.0; <\/pre>\n
MERGE with VALUES Clause<\/h2>\n
The MERGE<\/code> statement was added to Standard SQL several years ago. It was based on a proposal by ANSI representatives from Oracle and IBM, but forms of it had already existed in other products, though under a different name. The most common one was UPSERT<\/code> from Postgres. Let\u2019s jump right into it.<\/p>\n
One table expression is the target, the table you are trying to modify. The other table expression is the source, the table that provides the modifications. Presumably, you want the target to persist, but you don\u2019t need the source to persist after the updates and insertions are done.<\/p>\n
The MERGE<\/code> clause defines the target; the USING<\/code> clause defines the source. And the ON<\/code> clause matches the two tables. The WHEN [NOT] MATCHED ...THEN<\/code> clauses determine the action to be taken<\/p>\n
MERGE INTO Sales.Sales_Reasons AS Target\r\nUSING (VALUES ('Recommendation', 'Other'), \r\n          ('Review', 'Marketing'), ('Internet', 'Promotion'))\r\n  AS Source (new_sales_name, new_reason_type)\r\nON Target.sales_name = Source.new_sales_name\r\nWHEN MATCHED\r\nTHEN UPDATE SET reason_type = Source.new_reason_type\r\nWHEN NOT MATCHED\r\nTHEN INSERT (sales_name, reason_type)\r\nVALUES (new_sales_name, new_reason_type);<\/pre>\n
 <\/p>\n
It\u2019s easier to think of the MERGE<\/code> statement as a program, written as a single statement Instead of having IF\u2013THEN\u2013ELSE<\/code> logic or CASE<\/code> expressions in multiple statements.<\/p>\n
Obviously, updating makes sense only when there is a match, and inserting makes sense only when there is not a match. The standards allow for either of these clauses to include an optional \u2026 AND <search condition><\/code>, so you can add quite a bit of logic to this one statement. Technically, the WHEN clauses list can finish with the ELSE<\/code> IGNORE<\/code>; it acts as a placeholder just as the ELSE<\/code> clause did in the CASE<\/code> expression. Microsoft has more extensions to the syntax, and there have been some performance issues. If you are using it in SQL Server, I strongly suggest checking both the syntax and current performance in whichever version of SQL Server you\u2019re running.<\/p>\n
Conclusion<\/h2>\n
The VALUES<\/code> clause is ANSI standard and implemented by many relational database vendors. The VALUES<\/code> clause can save typing, and it\u2019s also the rare case when easier can mean better performance because the rows will be treated as a set.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
The VALUES clause makes inserting literal values into a table simple and easy. In this article, Joe Celko explains how to use the VALUES clause.…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143539],"tags":[5134],"coauthors":[6781],"class_list":["post-94465","post","type-post","status-publish","format-standard","hentry","category-featured","category-theory-and-design","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94465"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94465\/revisions"}],"predecessor-version":[{"id":94469,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94465\/revisions\/94469"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94465"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}