Data transfer - Creating column and adding values on the fly

jbaggaley

I have three databases:
RemoteDB. It contains the remote user's subset version of the Data
CentralMasterDB. Has exactly the same structure as the remote users tables and all the data.
ConflictDB. This contains a copy of the remote table structures but has an additional key in every table containg the remote machine name so that duplicates can exist. Once the data is transferred into them then they have decisions made on them by business objects.

I need to copy data from the remote pc to the conflict table and at the same time add the remote machine name to a column which only exists in the conflict db.

After this, my business objects process the potential conflicts and then any records which are no longer considered in conflict get transferred to the central master db losing their additional machine name column in the process.

Is there any way of interacting with the resultset after it has been compared to put this extra column in along with a machine name value? The only other ways I can think of doing this is to manually loop through your resultset datasets and add the values that way but this becomes very quickly complicated.

Thanks

Jon

david connell

Hi there,
I donot think that you can interact with the result sets in the way that you want to. However you could do the following

generate the result set (using the API)
then
set the default for the column (making sure that there is no column default)
execute the SQL
Remove the column default

The reason to verify that there is no default is to make sure that you have not got two scripts running concurrently or some other wierdness.

Hope that helps
David

jbaggaley

Thanks for your reply, I can see what you are saying about creating a default for an existing column but not sure whether it would work in practice yet.
Can you post a quick example of setting defaults for a column because there are no examples in the manual about this sort of area or even descriptions about how the object can be interacted with other than that it is part of the RedGate.SQLCompare.Engine Namespace.

Regards

Jon

david connell

Hi Jon,
Below are some examples of adding and removing constraints on a SQL2000 database. I have used the example Widgets Database and have used a the Description column in the WidgetsDescription Table.
The code below is just pure SQL, you would have to execute this before and after you ran the main execution block generated by the SQL Data Compare Engine.

To see if this column default exists:

SELECT name from sysobjects WHERE type = 'D' AND name = 'DF_WidgetDescriptions_Description'

To add the constraint:

ALTER TABLE WidgetDescriptions
ADD CONSTRAINT DF_WidgetDescriptions_Description
DEFAULT 'NY' FOR Description ;

To Drop the constraint:

ALTER TABLE WidgetDescriptions
DROP CONSTRAINT DF_WidgetDescriptions_Description

jbaggaley

In the end I created a redundant column in both the central and remote databases called remotemachineid which is filled in remotely on any save or update.
I then made use of custom key column mapping using a where clause to limit the rows expected to the remote machine id. The data then fitted into my staging area where I could process it, move non-conflict records to the central db and finally transfer the clean data back to the remote laptop.
Sounds hideously complicated but there are a lot of rules that have to be run to deal with conflict data due to our remote setup.

Thanks for your help

Jon