The basic INSERT statement is fully functional and thoroughly useful for inserting data into a row. There are advanced variations, though, that can be more convenient, depending on the need.
Using Multiple Value Clauses
One variation is to use a INSERT statement with multiple VALUE clauses. The syntax allows you to define one set of columns as the target of the inserts, and use one VALUES keyword - but provide multiple sets of values enclosed in parenthesis. Here is a syntax example:
Each values clause must
- contain the same number of values (and data types) as the number of columns being targeted
- must be enclosed in parenthesis
- separated by commas
All the lines are run as a single statement. The above example is the equivalent of creating and executing four separate INSERT statements, although the new syntax is much easier to use and maintain when there are a large number of inserts.
INSERT with SELECT
Another variation of the INSERT statement is to use a SELECT clause to provide the values. This is similar to the variation above where a single INSERT is used with multiple sets of values. Essentially, this variation does the same thing. The difference is that the VALUES keyword us NOT used, and the set of columns used for the values are not enclosed in parenthesis.
Here is an example of the syntax:
Again, the number and datatypes of the of columns used to provide the insert values must match the columns in the INSERT statement.
Of course, an advantage of this approach is that the query used to define the values can be as complex as necessary. For example:
Because the SELECT statement can be customized and made dynamic at runtime, this type of INSERT statement could be more easily used in a stored procedure.
SQL Functions and Query Filters
There are many, many ways to craft a query to return the desired data. Keywords such as TOP and DISTINCT have already been discussed.
Two additional useful filters are BETWEEN and LIKE.
The BETWEEN Comparison Operator
BETWEEN is like a set of comparison conditions. Here is a query that is filtering on a range of badgenumbers:
BETWEEN can be used to replace these two conditions. It is a shortcut to using >= (EQUAL OR GREATER THAN) and <= (LESS THAN OR EQUAL TO). Here is the type of query it replaces:
Notice that the column name for the comparison is only used once. Also, BETWEEN is inclusive, meaning that the results will include all rows that match the comparison values. In this case, where the badgenumber includes 1000 or 1050.
The LIKE Comparison Condition
LIKE is used to match a comparison column based on a partial string, and a wildcard character. There are three versions of LIKE, depending on how the wildcard character is used.
The wildcard character is "%". It means "any character". Here are syntax examples with the three possible placements:
Notice that the "=" sign is omitted with this statement.
When the wildcard character is positioned as the first character in the comparison string, the LIKE statement attempts to match the end the column values against the characters after the wildcard. In the example above, it will match any vendor name that ends in "sor". This would include values such as "Hysor", "Windsor" and "Messor", for example.
When the wildcard character is positioned as the last character in the comparison string, the LIKE statement attempts to match the beginning the column values against the characters before the wildcard. In the example above, it will match any vendor name that begins with in "win". This would include values such as "Winding Way", "Windsor" and "Winsoft", for example.
When the wildcard character is positioned at both the beginning and end of the comparison string, the LIKE statement attempts to match any record where column value contains the comparison string - anywhere in the string. In the example above, it will match any vendor name that begins with "ind". This would include values such as "Winding Way", "East Wind" and "Indiana Pacers", for example.
The IN Comparison Condition
The IN comparison condition is a shortcut and more convenient method to use multiple OR operators. Take this query, for instance
This can be rephrased using the IN operator as follows:
The set of comparison values must be the same data type as the column being comapared against. Values are separated by commas.
The example uses a list of literals in the parenthesis. This list can also be defined using a query. Take the following, for example:
The NOT Operator
For some comparison operators, the filter can be written using the reverse condition. For example:
For other cases, especially using BETWEEN, LIKE and IN, the NOT operator is used to reverse the condition. For example:
This query would return all rows where the vendor is NOT IN the listed states.
This query would return all employees with badge numbers outside of the specified range.