A while back, Hilary Stoupa wrote an excellent, detailed blog post about modifying a database table with existing data to work with a DBXL solution. I highly recommend reading it if you are faced with that sort of situation, as it is the go-to guide for blending existing data with new data from DBXL. Here is the link:
http://www.infopathdev.com/blogs/hilary/archive/2009/10/13/use-dbxl-submit-with-existing-sql-data.aspx
In this blog post, I would like to present a few tweaks to her design that might work a little better for some people in some cases. The first is a modification that allows you to have a primary key on your tables and allows easily identifying which data came from your original data, and which came from DBXL. The second tweak is a third alternative to using the trigger and stored procedure approaches Hilary described in her blog.
An IsOriginalData field
Suppose you have a database table with existing data that looks like the image below, and you want to be able to (a) Add data to it using DBXL and (b) Use DBXL to modify the existing data
As in Hilary's blog post, the first step would be to add a new column to store the DBXL DocId, whenever relevant
Once you've added the DocId column, you can now add a computed column that will allow us to identify which rows are original data, and which are data from DBXL, based on whether there is a DocId present or not.
Create a column called IsOriginalData, and in the column's properties, expand the Computed Column Specification setting, and give it this formula:
ISNULL(CASE WHEN DocId IS NULL THEN CAST (1 as BIT) ELSE CAST(0 as BIT) END, CAST(0 as BIT))
The CASE statement will result in a TRUE value when DocId is null (i.e., for original data), and to FALSE when DocId is present. There is an ISNULL function wrapped around this to ensure SQL server that the computed value for this field will always be non-null. And the reason we want to do this is in order to be able to use this field as part of the table's primary key, which requires that all of its fields be non-null.
While holding the control button, select all of the columns that are currently in the table's primary key, and select the IsOriginalData field as well. Right-click any one of the, and select Set Primary Key. This will place a primary key on all of these fields.
In so doing, you will have a new primary key that is only slightly less restrictive than your current primary key. This will allow your existing data and DBXL data to coexist for a brief moment until the trigger Hilary described in her blog post has time to run, or it will allow both to coexist long-term for the approach described in the next section.
Managing DBXL data and existing data with a view
Hilary's blog post focuses on using a trigger to remove existing data whenever data from DBXL is added, and at the end, presents an alternative approach of calling a stored procedure from InfoPath to remove existing data just before corresponding data is added to DBXL.
There are people who would like to avoid using triggers in their database, and there are good reasons to avoid calling SQL directly from InfoPath (as in the case of the stored procedure approach).
One third approach is to allow the old and new data to coexist in the original table, and use a database view to only expose data when it is either (a) data saved from DBXL or (b) original data that has no corresponding DBXL data.
This carries the added benefit that you will have all of your original data, untouched, in case something should go awry at any point.
The steps belos assume that you have carried out the modifications in the first half of this blog post, but the approach can also be applied, with some small modifications, directly to Hilary's instructions.
The first step is to create a new view based off your DB table. Right-click the Views folder in SQL Server Management Studio, select New View..., select your table, click Add, and then close.
In the designer, check the (All Columns) box to include all columns in your view. This will create a query like the one below in the query editor:
Use the query editor to modify this query to make one analogous to the one below. You should be able to make a query to fit your table by replacing ExistingData with the name of your table, and modifying WHERE clause to match your original ID field(s) between the two aliases in the query.
In essence, this query selects any rows in the table that (a) are from DBXL [(IsOriginalData = 0)] or (b) are original data and have no matching DBXL data (the NOT EXISTS clause).
Here are the contents of my example table:
Note that there is a row of existing data, and a row of data from DBXL with the OriginalId value "ABCDE." When we look at the data through the view, we can see that this row of existing data is filtered out, and everything else is displayed:
That's it! Enjoy!