Merging two Sitecore Web Forms for Marketers databases

Recently I was involved with the upgrade of a Sitecore solution which also included upgrading the Web Forms For Marketers (WFFM) module. The live Sitecore upgrade required me to run the live site in tandem with upgrading a copy of the site which at the end I would switch over.

Putting a content freeze in place was not an issue with exception of data which is captured from website users, i.e. the WFFM submissions. I needed a way to merge the data from the pre upgraded site into the upgraded version once the new site was live.

After consulting Google and raising a Sitecore support ticket I did not have a solution other than a suggestion from Sitecore of following this  which was how to generally merge two databases together of the same schema.

I decided to look at the database schema to see if this would be possible. My first problem was that the website I was working on (inherited) had implemented WFFM using the SQL Lite database which I was surprised to find is not that easy to examine. Straight away I wanted to migrate these to MS SQL databases and followed this post by Ivan Buzyka.

Looking at the data in the database, you will find three tables:

• Form
• Field
• Pool

It was quite clear that Form stored a row for each form submission and that Field stored a row for each form field of each submission with foreign keys matching these up. Primary keys are Guids so my first thoughts were that I can simply move data from one database to another without any problems.

But looking at the Pool table I could see that this was different. I tried to find an explanation of what this table is for but again did not find anything. I had my assumption based on the columns and wrote a SQL statement to prove it. What I found was that the Pool table is the Field table results but aggregated to give counts of how many instances there are of each response to each field. Meaning that if you had a field which was a simple yes/no response and you had 5 form submissions with 3 yes responses and 2 no responses you would have two rows in the Pool table each with the associated counts.

This meant that merging the data was a little trickier for the Pool table as just merging would result in multiple rows representing the same field/response pair.

I handled the merging of the data after the launch of the upgraded site and the approach I took was to have a blank database for the upgraded website at initial launch meaning that the two databases I had did not have any overlap and I could import the data from the Form and Field tables of the old database to the new one. Note: You have to merge Form before Field due to constraints.

I then wrote the following SQL script which generates the data for the Pool table based on the content of the Field table. This means that the existing Pool data in both databases can be deleted.

DELETE FROM Sitecore_WebForms.dbo.[Pool]
GO

WITH AggregatedData (FieldId, Occurrence, Value)
AS
(
SELECT 
DISTINCT 
FieldId,
(SELECT 
COUNT(*) FROM Sitecore_WebForms.dbo.Field f1 
WHERE f1.value = f.value COLLATE Latin1_General_CS_AS and f1.FieldId = f.FieldId) AS Occurrence, 
Value COLLATE Latin1_General_CS_AS AS Value
FROM Sitecore_WebForms.dbo.field f
)

INSERT INTO Sitecore_WebForms.dbo.[Pool] (Id, FieldId, Occurrence, Value)
SELECT (SELECT NEWID()) AS Id, * FROM AggregatedData

Following the merge and generation of the Pool data I was able to verify within the Form Manager in Sitecore that both old and new data was visible. For reference I was dealing with WFFM v2.4 rev.150619.