Author: Ian

SQL Server Error 110: There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Posted on by Ian

Error message 110 is a commonly encountered error in SQL Server when inserting data into a table. The full error looks like this:

Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This happens when you specify less columns in the INSERT statement than the number of values that you’re trying to insert with the VALUES clause.

This will occur if you accidentally omit one or more columns from the INSERT statement.

You’d get a similar (but technically different) error if you tried to do the opposite – specify more columns in the INSERT statement than you try to insert.

Continue reading →

How to Execute a Trigger Only When a Specific Column is Updated (SQL Server)

Posted on by Ian

In SQL Server, you can create DML triggers that execute code only when a specific column is updated.

The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated.

You can do this by using the UPDATE() function inside your trigger. This function accepts the column name as its argument. It returns a boolean.

Continue reading →

Send Email from a Trigger in SQL Server (T-SQL)

Posted on by Ian

If you find yourself needing to send an email automatically upon certain events occurring in SQL Server, you can do this via a trigger.

For example, you could automatically send an email when somebody deletes or updates a record from a table, etc.

To do this, you need to create a trigger that includes code for sending the email upon the required event.

Continue reading →

Create an “Instead Of” Trigger in SQL Server

Posted on by Ian

When you create a trigger in SQL Server, you have the option of firing it in conjunction with the triggering statement (i.e. the SQL statement that fired the trigger), or firing it instead of that statement.

To fire the trigger instead of the triggering statement, use INSTEAD OF argument.

This is in contrast to using the FOR or AFTER arguments. When you use those arguments, the trigger fires only when all operations specified in the triggering SQL statement have launched successfully.

Continue reading →

Create a Partitioned Table in SQL Server (T-SQL)

Posted on by Ian

SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.

Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup/s that will hold each partition. You also need to create a partition function and a partition scheme.

So it goes like this:

  1. Create filegroup/s
  2. Create a partition function
  3. Create a partition scheme
  4. Create the partitioned table

Below is an example of using these steps to create a table with four partitions.

Continue reading →

SQL Server Error 213: Column name or number of supplied values does not match table definition.

Posted on by Ian

Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Continue reading →