Is it possible to exclude a column when inserting data?
paulchapman
Posts: 4
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:
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) :
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
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
