Sql-server – Violation of PRIMARY KEY constraint in SSMS import/export wizard

sql server

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 null if 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?

Best Answer

Start by narrowing it down. Does it happen with only this one table? What if you play around with the scripting options (still only doing this one table); uncheck "everything". Etc. Until you have something working and then add bits and pieces back on until you find which one breaks it.

In the end, this is either a problem in the wizard (since duplicates cannot occur in the source data when you have a unique index), or we don't have the full story (hence my recommendation to narrow it down).

If it is a problem in the wizard, then consider reporting it to MS. They will likely want a repro for your problem.

Related Question