Violation of PRIMARY KEY constraint in SSMS import/export wizard

Question

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?

Answer 1

I got this problem also. It turned out that I had a case sensitive collating sequence (SQL_Latin1_General_CP1_CS_AS) on the source column and a case insensitive one (SQL_Latin1_General_CP1_CI_AS) on the target. Change the target fixed the issue.

Answer 2

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.

Answer 3

This ended up being a bug(?) with using the SQL Server Native Client 11.0 for both the data source and destination. I changed the data source to use the OLE DB Driver for SQL Server instead, and it worked normally using Enable ID insert.

For reference, my situation was:

• Two instances of SQL Server, 2017 and 2019
• A dirty version downgrade of this DB from 2019 to 2017