Archives

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.

CREATE TABLE [dbo].[SORELS_EXT](
	[Identity_Column] [int] IDENTITY(1,1) NOT NULL,
	[Timestamp_Column] [timestamp] NOT NULL,
	[FKey_ID] [int] NOT NULL,
	[COLOR] [char](10) NULL,

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.

INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
           ([FKey_ID]
           ,[COLOR])
     VALUES
           (<FKey_ID, int,>
           ,<COLOR, char(10)>)
GO

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.

Therefore, we could insert one record at a time manually like this:

INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
           ([FKey_ID]
           ,[COLOR])
     VALUES
           ('12345'
           ,'Blue')
GO

Make sure you delete the inserted records between insert attempts. The next step is to perform an easy insert from a select statement such as:

INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
           ([FKey_ID]
           ,[COLOR])
     select Identity_Column, 
     'Blue' from sorels
 
GO

This inserts a record into the extension table for every record in the parent and inserts a color as well. Verify that it worked and delete the records. Next, flesh out your select statement like so:

INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT](
                                            [FKey_ID], [COLOR])
  SELECT Identity_Column, CASE
                            WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
                            WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
                            ELSE 'White'
                          END
  FROM SORELS SOR
GO

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:

Error Date and Time Removed SQL Server Database Error: Violation of PRIMARY KEY constraint ‘PK_SORELS_EXT’. Cannot insert duplicate key in object ‘dbo.SORELS_EXT’. 4 0

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:

UPDATE SORe
SET COLOR =
      CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END
FROM   M2MDATA01.dbo.SORELS SOR
     INNER JOIN
       M2MDATA01.dbo.SORELS_EXT SORe
     ON SOR.IDENTITY_COLUMN = SORe.FKEY_ID

Then follows insert statement, which needs to check if an extension record already exists:

INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
           ([FKey_ID],
            [COLOR])
SELECT identity_column,
       CASE
         WHEN Left(sor.fpartno,2) = 'BL'
         THEN 'Blue'
         WHEN Left(sor.fpartno,2) = 'RD'
         THEN 'Red'
         ELSE 'White'
       END
FROM   m2mdata01.dbo.sorels sor
WHERE  sor.identity_column NOT IN (SELECT fkey_id
                                   FROM   sorels_ext)
 
GO

This effectively inserts all missing records into the extension table. The preceding scripts work in all SQL versions beyond 2000.

How Can I “Upsert” with SQL 2008? What is the Merge statement?

I’m glad you asked. With SQL 2008 you may want to use the Merge statement.

MERGE Sorels_ext AS SORe
USING (SELECT SOR.identity_column
,             CASE
                WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
                WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
                ELSE 'White'
              END
                AS colors
       FROM Sorels AS SOR) SOR1
ON (SORe.fkey_id = SOR1.identity_column)
WHEN MATCHED
THEN
  UPDATE SET SORe.fkey_id = SOR1.identity_column, SORe.Color = SOR1.colors
WHEN NOT MATCHED
THEN
  INSERT (
            fkey_id, Color)
  VALUES (
            SOR1.identity_column, SOR1.colors);

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.

Any questions or suggestions?

Related posts:

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

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>