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
LVL 3
peterhuppAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.