• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

Cannot insert duplicate key in object after deleting and restoring from a older data

I am trying to restore the visits from a backup database to the live database.  I am deleting all the visits for a client then restoring the visits from the backup. (client screwed things up).  BUT, getting an error: Violation of PRIMARY KEY constraint 'PK_Visit'. Cannot insert duplicate key in object 'dbo.visit'.

It is as if the visit records were still there.  Is there a way to PACK the table so the deleted visits are completely gone?

CODE:

delete from [LiveDB].[dbo].visit where clientno= 100

select * into #tmpVisit from [BackupDB].[dbo].[visit] where clientno= 100

insert into [LiveDB].[dbo].[visit] select * from #tmpvisit
0
peterhupp
Asked:
peterhupp
  • 3
  • 2
1 Solution
 
StefanLambdaCommented:
probably the 'PK_Visit' is an identity column of the table.

You should use SET IDENTITY_INSERT … { ON | OFF }

see http://msdn.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx

Stefan
0
 
peterhuppAuthor Commented:
Did the "set identity_insert visit off" and got the following message.  It is a

Table 'visit' does not have the identity property. Cannot perform SET operation. Column properties of pk_visit
0
 
StefanLambdaCommented:
Why do you use a #tmpVisit ?
You can use this
      delete from [LiveDB].[dbo].visit where clientno= 100
      insert into [LiveDB].[dbo].[visit] select *  from [BackupDB].[dbo].[visit] where clientno= 100

Else did you check if you haven't several records with the same Key in your #tmpVisit?
Perhaps the columns order changes between [LiveDB].[dbo].visit /  [BackupDB].[dbo].[visit] and #tmpVisit?


Could you provide the create statement for the 3 tables?
0
 
peterhuppAuthor Commented:


/****** Object:  Table [dbo].[visits]    Script Date: 04/01/2011 16:06:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[visits](
      [chngreas] [char](30) NOT NULL,
      [authno] [char](10) NOT NULL,
      [changedby] [char](5) NOT NULL,
      [clientno] [char](8) NOT NULL,
      [date] [datetime] NULL,
      [fromtime] [numeric](4, 0) NOT NULL,
      [notes] [text] NOT NULL,
      [recno] [int] NOT NULL,
      [clientsfk] [int] NOT NULL,
      [usr_fk] [int] NOT NULL,
 CONSTRAINT [PK_visits] PRIMARY KEY NONCLUSTERED
(
      [recno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

As you can see the primary key is actually RECNO (dumb field name I know but choose it decade ago).  The restoration that I am trying to do is from a back up 2 days ago. so the structure of the files should be the same

As for the simplified insert, tried that first.

0
 
peterhuppAuthor Commented:
Giving credit to you for this comment because this was the most likely solution.  But it was my assuming that the client didn't change any clients on visits which lead to the duplicates.  My bad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now