Archives

Great Links

Using Insert, Update, “Upsert”, and Merge to Manipulate FastForms Fields.

Yesterday I explained the basics of how FastForms deals with SQL Server. Today I’ll discuss the skills they don’t teach you in M2M classes; how to manipulate SQL directly and save your company thousands in manual data entry.

How do I automatically populate my extension table?

Continuing our hypothetical situation from yesterday, our employees at Acme Inc. do not want to have to go through all of their records to update them. This is tedious and actually impossible if the sales orders have been closed. Therefore, Acme wants the color value to be automatically entered for all sales orders already in the system. How does one accomplish this? I thought you’d never ask.

If you take a look at the create table statement, which can be obtained by right clicking on the table and choosing Script Table as, Create To, New Query Editor Window; you will see the fields that were created.

Our table has 4 fields. The Identity_Column and Timestamp_Column are automatically assigned. The FKey_ID is a foreign key to identity column of the parent table, and the Color field is obviously our data. Knowing all of this, how do we insert the correct data?

Crafting a SQL Insert Statement Step by Step

For simplicity’s sake, we’ll create the insert statement automatically in the same way we generated the create statement. This step may seem trivial, but this saves time when dealing with a large table.

Why aren’t Identity_Column or Timestamp_Column listed? As I mentioned before, Identity_column is an auto-number column and will generate an error if you attempt to manually assign it without using Set Identity_Insert On. We don’t’ need to bother with that. Timestamp_Column is also auto generated as well.

The code creates all of the records in your extension table and the correct color.

What if I need to add fields to an already existing extension table? What is an “upsert”?

What if you have an existing extension table where some of the records already exist? Let’s say that Acme already had a field in the extension table called FuseLength. Some of the parts already have a value for FuseLength and some don’t. Well, that is a little more complicated. If you attempt to insert a record for color that already exists (has a value for FuseLength), you will receive an error similar to this:

The script should update records that already exist and insert the others. It’s important to note that all FastForms extension fields are nullable, meaning that a value is not required. If you are using SQL 2000 or 2005, the simplest way to perform this is with two separate statements. The combination of them is often referred to as an “upsert.”

The first statement updates the existing records with the correct color:

I know it looks complex, but once you understand the syntax, the Merge statement can be easier to write as well as more efficient for the server. The target table (table being acted upon) follows the MERGE statement, in our case Sorels_Ext. The source of the data follows the USING statement in our case Sorels. Notice that I aliased the CASE which determines color with the word colors to make things simpler to read. The ON clause specifies the link between the tables. The WHEN MATCHED statement specifies the action to occur if a record is found in both tables, and WHEN NOT MATCHED is executed when a record is missing from the target table.

This is executed as one statement and should theoretically be faster since the tables are only touched once.

In summary, in these two articles we’ve created a FastForms customization and automatically populated the fields in it using Insert, Update, and Merge.

[…] to load slowly changing dimensions, facilitate auditing, as well as other uses. I briefly discussed how to use Merge some time ago, but I wanted to take a deeper dive into it because it is so darn […]