Sql insert problem

I have a program that uses sql and some corrupt data was somehow entered.  I copied all today entries to a temp table and fixed the problem.  I am trying to copy it back, but running into a contraint problem.  The original table has a key and the temp table did not.

Here is the insert statement
INSERT INTO TimeSheet (timeID, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser)
SELECT timeID, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
    FROM timeshee2
    WHERE year(activitydate) = 2009 and month (activitydate) = 4 and day(activitydate) = 15

Here is the error message
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__TimeSheet__49C3F6B7'. Cannot insert duplicate key in object 'dbo.TimeSheet'.
The statement has been terminated.

How can I ignore that contraint and re insert those records.
red_75116Asked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
Are you inserting into a SQL Server 2005 backend? If yes, try the statement batch below -

(test it with a rollback initially before running with commit)
DECLARE @MaxId int
BEGIN TRAN
SELECT @MaxId = MAX(timeID) FROM TimeSheet
INSERT INTO TimeSheet 
	(timeID, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser)
SELECT	timeID + @MaxId, ActivityDate,CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
FROM
	(SELECT ROW_NUMBER() OVER (ORDER BY timeId) + @MaxId AS timeId, ActivityDate,CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
	    FROM timeshee2
	    WHERE year(activitydate) = 2009 and month (activitydate) = 4 and day(activitydate) = 15
	) Tmp
COMMIT

Open in new window

0
 
reb73Commented:
It is not advisable to ignore a primary key constraint..  What is/are the Primary Key field in your target table (Timesheet)?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in other terms: you have to ensure that the insert specifies a really unique value for the rows to be inserted.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ajexpertCommented:
I believe timeID is the primary key and the value you are trying to insert already exists, so is the violation.
Is timeID has a sequence associated to it?  If yes you can use

INSERT INTO TimeSheet (timeID, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser)
SELECT <timeID_sequence_name>.NEXTVAL, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
    FROM timeshee2
    WHERE year(activitydate) = 2009 and month (activitydate) = 4 and day(activitydate) = 15
    
 
 
if timeid is not sequence, you have to get max timeid and increment by 1
something like:
 
INSERT INTO TimeSheet (timeID, ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser)
SELECT (SELECT MAX(timeID) + 1 FROM TimeSheet), ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
    FROM timeshee2
    WHERE year(activitydate) = 2009 and month (activitydate) = 4 and day(activitydate) = 15
    

Open in new window

0
 
red_75116Author Commented:
The timeid is basically a serial number that is unique for these records.  When I moved them to the temp directory, users kept making records and there is now a break in the sequence.  That doesn't seem to be affecting the program usage though.  I would like to move these records back and fill in the gaps.

I don't care if it assigns new numbers or not, but I need them moved back.  I tried the second insert statment and I get this message
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smallmoney.
The statement has been terminated.
0
 
reb73Commented:
Then take out the timeId field in the insert statements as follows (I presume it is an identity field)-
INSERT INTO TimeSheet (ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser)
SELECT ActivityDate, CliID, HID, ClientName, CurrentStatus, STATUS_IN, STATUS_OUT, STATUS_MEETING, STATUS_CONF, STATUS_LUNCH, STATUS_OFF, STATUS_VAC, Back_At, STATUS_MSG, OooReason, tssAddDate, tssCreateUser
    FROM timeshee2
    WHERE year(activitydate) = 2009 and month (activitydate) = 4 and day(activitydate) = 15

Open in new window

0
 
red_75116Author Commented:
reb73,

that gives me this error
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'timeID', table 'SQL2005_281054_time.dbo.TimeSheet'; column does not allow nulls. INSERT fails.
The statement has been terminated.

0
 
8080_DiverCommented:
If the data in the original table was copied to the work table, modified, and is now ready to copy back, why not use an UPDATE statement (joining via the TimeID) to replace the corrupted data in the original table.  THat way, you don't have the corrupted data to deal with later?
0
 
red_75116Author Commented:
I deleted the data from the original table because it was causing the program to crash. Now I just need to move it back.
0
 
reb73Commented:
Did you try my suggested solution in post 24151278?
0
 
8080_DiverCommented:
Wrap  your insert statement (the one that inserts the TimeID's also) with the statement s as shown in SQL_1 below.
SQL_1:
 
SET IDENTITY_INSERT TimeSheet ON
your statement goes here
SET IDENTITY_INSERT TimeSheet OFF

Open in new window

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.

All Courses

From novice to tech pro — start learning today.