Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql insert problem

Posted on 2009-04-15
11
Medium Priority
?
461 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:red_75116
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 25

Expert Comment

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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24151067
in other terms: you have to ensure that the insert specifies a really unique value for the rows to be inserted.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24151073
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:red_75116
ID: 24151142
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
 
LVL 25

Expert Comment

by:reb73
ID: 24151161
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
 

Author Comment

by:red_75116
ID: 24151176
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
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24151278
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24151511
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
 

Author Comment

by:red_75116
ID: 24151588
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
 
LVL 25

Expert Comment

by:reb73
ID: 24160838
Did you try my suggested solution in post 24151278?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24160955
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question