More Inserts and Filters

Introduction

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:

INSERT into xxx.vendors (vendorname, statecode) VALUES ('ACME, Inc.', 'OH') ,('Seldon Ltd.','KY') ,('Corse Electronics','NY') ,('Flybee Ent.','MN');

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:

INSERT into xxx.vendors (vendorname, statecode) SELECT companyname, state from xxx.importedcompanies;

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:

INSERT into xxx.vendors (vendorname, statecode) SELECT TOP 20 companyname, state from xxx.importedcompanies WHERE state in ('CA','AZ','NM') and creditlimit>5000;

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:

SELECT employeename, badgenumber, departmentname from employees where badgnumber >= 1000 and batchnumber <= 1050;

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:

SELECT employeename, badgenumber, departmentname from employees where badgnumber BETWEEN 1000 and 1050;

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:

SELECT vendorname from xxx.vendors WHERE vendorname LIKE '%sor'; SELECT vendorname from xxx.vendors WHERE vendorname LIKE 'win%'; SELECT vendorname from xxx.vendors WHERE vendorname LIKE '%ind%';

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

SELECT vendorname from xxx.vendors WHERE state='OH' OR state='WI' OR state='MN' OR state='MI';

This can be rephrased using the IN operator as follows:

SELECT vendorname from xxx.vendors WHERE state IN ('OH','WI','MN','MI');

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:

SELECT vendorname from xxx.vendors WHERE state IN (select statecode from xxx.states where region='Midwest');

The NOT Operator

For some comparison operators, the filter can be written using the reverse condition. For example:

SELECT vendorname from xxx.vendors WHERE creditlimit >= 5000; SELECT vendorname from xxx.vendors WHERE creditlimit < 5000;

For other cases, especially using BETWEEN, LIKE and IN, the NOT operator is used to reverse the condition. For example:

SELECT vendorname from xxx.vendors WHERE state NOT IN ('OH','WI','MN','MI');

This query would return all rows where the vendor is NOT IN the listed states.

Another example:

SELECT employeename, badgenumber, departmentname from employees where badgnumber NOT BETWEEN 1000 and 1050;

This query would return all employees with badge numbers outside of the specified range.