I have table MYTABLE with one PK constraint, generated by a third-party app:
CREATE TABLE [dbo].[mytable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FIELD_A] [nvarchar](255) NOT NULL, [FIELD_B] [tinyint] NOT NULL, CONSTRAINT [mytable_PK] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY], UNIQUE NONCLUSTERED ( [FIELD_A] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] And the data is pretty simple:
ID FIELD_A FIELD_B 1 abc 0 2 dfgh 0 3 foo 0 I’m trying to import this data into an empty copy (created with generate scripts wizard/above ddl) of this table in another database using data import, but get the following error:
"Violation of PRIMARY KEY constraint 'mytable_PK'. Cannot insert duplicate key in object 'dbo.mytable'" I have tried the following (to verify this isn’t a duplicate question):
- Check that there is no actual duplicate in the destination: The destination is empty
- Enabled option "Enable identity insert" (if disabled, this fails with
Violation of FOREIGN KEY...instead) - Attempted with both ‘Delete existing rows’ and ‘Append’
- Disabled constraint checking:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' - Attempted to not import the surrogate ID – this fails as import wizard sets field to
nullif ignored instead of leaving empty. - Checked for triggers that may be creating duplicate inserts
- Forced SSMS to just drop and recreate the tables
What else can I check to troubleshoot this?
