Is it possible to exclude a column when inserting data?

paulchapmanpaulchapman Posts: 4
edited August 29, 2007 9:30AM in SQL Toolkit Previous Versions
Hi,

We have been using Redgate to merge data between two databases.

We have a problem with one particular table (subscribers) that has an identity column (subscriber id) and a primary key constraint (pk_subscribers).

We don't want to map on this key so we define the following custom mapping:
tableMapping = (TableMapping)mappings.TableMappings.Join(db1.Tables["[dbo].[subscribers]"], db2.Tables["[dbo].[subscribers]"]);

tableMapping.MatchingMappings.Clear();
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["account"]);
tableMapping.RefreshMappingStatus();

When we come to run the sql (our options only allow update/insert statements), we try to insert the following into db2.subscribers (i've omitted 50 other columns for clarity) :
INSERT INTO [dbo].[subscribers] ([account],  [subscriber_id] VALUES ('100100', 1 )

In db2, we already have an entry for a different account number with subscriber_id 1.

Is there a way we can insert the data without getting the error:

[2627] Violation of PRIMARY KEY constraint 'pk_subscribers'. Cannot insert duplicate key in object 'subscribers'.

Any help would be appreciated.

regards,

Paul Chapman
Sign In or Register to comment.