0

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?

7
  • Have you removed IDENTITY attribute on the second table?
    – McNets
    Oct 15 '20 at 13:15
  • @McNets Do you mean create the blank table without IDENTITY (1,1) and add it back later, or try and choose not to import the ID field?
    – Cpt.Whale
    Oct 15 '20 at 13:23
  • IMHO you should never add an IDENTITY column to the destination table, unless you are using transactional replication and you have flagged it as NOT FOR REPLICATION.
    – McNets
    Oct 15 '20 at 13:28
  • Thanks @McNets, do you know if this type of thing is something I can specify in the create scripts wizard? I rely on the script creation as I need to do this for about 150 tables.
    – Cpt.Whale
    Oct 15 '20 at 13:33
  • Why don't use transactional replication? I think it worth for a volume of 150 tables.
    – McNets
    Oct 15 '20 at 13:35
0

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.

3
  • Yes it seems to be mostly a process issue, but I'm really looking for specific recommendations. It's not just this one table - I'm currently trying different connection types since this does seem like buggy behavior.
    – Cpt.Whale
    Oct 15 '20 at 14:20
  • I was about to ask for the bigger picture and recommend backup/restore, but I just read your comment on downgrade. But without a repro for us, you are really looking for someone who ran into that very same issue, I guess. I haven't, I'm afraid... (?). :-) Oct 15 '20 at 16:57
  • Or cheat and change the DDL to IGNORE_DUP_KEY = ON Oct 15 '20 at 20:28
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.