December 5, 2008 at 5:29 pm
I tried the following code:
Insert into timereporting.dbo.ptodetails
( EmpName,
PTODate,
PTOHours
)
(
Select
Technician,
CONVERT(CHAR(10), Started, 101),
NonBillableHours
from timereporting.dbo.servicedetails
where casetitle IN ('PTO', 'PTO-Unpaid')
)
--12/03/08 begin error logic
IF @@error <> 0
BEGIN
GOTO IGNORE_ERROR
END
IGNORE_ERROR:
However, I got the following error message:
• An error has occurred during report processing.
o Query execution failed for data set 'ServiceActivities'.
Violation of PRIMARY KEY constraint 'PK__PTODetail__690797E6'. Cannot insert duplicate key in object 'dbo.ptodetails'. The statement has been terminated.
This is a report I sometimes need to rerun, but I only want to collect the "PTO" data once, so I was attempting to allow the error and continue processing.
Can anyone help me? Thanks!
December 5, 2008 at 6:09 pm
What's the primary key? What are you planning to do in the ignore error? Do you want to load the duplicates?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2008 at 6:35 pm
The primary key is EmpName and PTODate. Ignore error is simply a label to allow me to continue on with the code. What I'm trying to do is to ignore the error in case the report is rerun.
December 5, 2008 at 6:36 pm
Sorry, I missed one of your questions. I do not want to load duplicates - I just want to ignore them and move on with the rest of the script.
December 5, 2008 at 8:04 pm
Your first issue is that you are thinking procedurally, not in sets. SQL Server is inserting or attempting to insert ALL the rows at once, so there is nothing to continue processing once you get the error. A better way to handle this is to eliminate the duplicates.
Something like this:
;With cteDupes As
(
Select
Row_Number() Over (Partition By Technician, CONVERT(CHAR(10), Started, 101) Order By Technician) as row_id,
Technician,
COnvert(Char(10), Started, 101) as PTODate,
NonBillableHours
From
timereporting.dbo.servicedetails
Where
casetitle in ('PTO', 'PTO-Unpaid')
)
Insert into timereporting.dbo.ptodetails
(
EmpName,
PTODate,
PTOHours
)
Select
Technician,
PTODate,
NonBillableHours
from
cteDupes
where
Row_Id = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 6, 2008 at 4:55 pm
Just add the "IGNORE DUPES" option to the Primary Key constraint.
--Jeff Moden
December 7, 2008 at 9:23 am
Thank you for your detailed reply. I think I understand what you are saying when you say that I am thinking procedurally and not terms of sets. I am an old COBOL programmer.
When you say to eliminate duplicates, I assume you mean that I should redefine the table to allow duplicates. Is that correct?
Unfortunately, I do not understand the code you added, beginning with ";With cteDupes As." Is that code's purpose to eliminate duplicates?
I looked up Partioning and even semi-colons in my SQL book, but couldn't make sense of the code. Could you give me any hints as to what the statements do?
Thanks so much for your time. I really appreciate your help.
December 7, 2008 at 10:04 am
Patricia Monreal (12/5/2008)This is a report I sometimes need to rerun, but I only want to collect the "PTO" data once, so I was attempting to allow the error and continue processing.
Can anyone help me? Thanks!
A couple of questions:
1) Why are you loading a table for the report? Wouldn't it be better to have the report just use the query to get the data?
2) If you really need to pre-populate a table for the report, why don't you truncate the table and reload all data? This will insure that you always have up-to-date information for the report.
3) And finally, if this table is temporary - why not use a temporary table instead?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 7, 2008 at 10:09 am
Patricia Monreal (12/7/2008)
Thank you for your detailed reply. I think I understand what you are saying when you say that I am thinking procedurally and not terms of sets. I am an old COBOL programmer.When you say to eliminate duplicates, I assume you mean that I should redefine the table to allow duplicates. Is that correct?
Unfortunately, I do not understand the code you added, beginning with ";With cteDupes As." Is that code's purpose to eliminate duplicates?
I looked up Partioning and even semi-colons in my SQL book, but couldn't make sense of the code. Could you give me any hints as to what the statements do?
Thanks so much for your time. I really appreciate your help.
Heh... welcome to a "different" world of data, Patricia.
Yes... Jack's code contains a "Common Table Expression" (most folks just call them "CTE") that he named "cteDupes". That CTE adds repeating "Row_Number"s (and are assigned the column name of Row_ID) starting at 1 and restarting at 1 every time the value in the Technician column changes according to the "Order By" in the Row_Number function.
Then, the "Select" below that only returns non-duplicates because only those items with a Row_ID of 1 from the CTE are used according to the WHERE clause of "Row_Id = 1".
The "WITH" pre-cursor identifies one or more CTE's which are nothing more than queries whose result set is used as if it were a Table in the FROM clause of the final query that follows.
My suggestion was to change the Primary Key definition to simply reject any duplicates.
Since you're kinda new at this, lemme ask... has anyone told you about "Books Online"? It's the "Help" system that comes with SQL Server. You can look a lot of this stuff up without having to buy expensive books.
--Jeff Moden
December 7, 2008 at 10:18 am
Hi Jeff,
I read and replied to Jack's response before I saw yours, but thanks for explaining it in such detail. That was very helpful. However, I like the idea of just adding an IGNORE DUPES clause to my table definition. I'm not exactly sure where to put it, so I will enclose my create statement and ask you, if you don't mind to show me precisely where it goes.
USE [TimeReporting]
GO
/****** Object: Table [dbo].[PTODetails] Script Date: 12/07/2008 12:14:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PTODetails](
[EmpName] [varchar](30) NOT NULL,
[PTODate] [char](10) NOT NULL,
[PTOHours] [decimal](18, 2) NOT NULL,
PRIMARY KEY CLUSTERED
(
[EmpName] ASC,
[PTODate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I just found Books Online and I will certainly use it as a reference in the future. Thanks so much for suggesting it and for you help. You're a lifesaver!
Pat
December 7, 2008 at 10:22 am
Jeff,
Thanks for responding. We run this report daily for the previous day, and then weekly for the previous week. Once a week is run, we never run that week again. Therefore, when the report is run, I am accumulating Personal Time Off in it's own table so that it can be accessed for a quick report anytime.
One respondent suggested that I simply add IGNORE DUPES to my table definition. That seems like the easiest approach, so I am going to try that.
Thanks very much for responding.
Pat
December 7, 2008 at 10:42 am
Okay, so you generate reporting tables and then use the reporting tables for various reports. Instead of trying to fiddle with IGNORE_DUP_KEYS on the index, I would suggest that you modify the process of loading the table in the first place.
If this table contains data for a single day only, you should be able to truncate the table and reload every time you rerun the process to load the table.
If this table contains data that is historical in nature (e.g. you add data each day), then you should be able to do something like:
DECLARE @loadDate datetime;
SET @loadDate = dateadd(day, datediff(day, 0, current_timestamp) - 1, 0); --yesterdays date
DELETE FROM dbo.PTODetail WHERE PTODate = @loadDate;
INSERT INTO dbo.PTODetail ...
Without further information on how you are using the table, that is the best guess I can come up with. So, either truncate all the data and reload, or delete the data you are going to load before running the insert. This model should allow for reloading at any time.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 7, 2008 at 10:44 am
Thanks, Jeff.
December 9, 2008 at 10:35 pm
Jeffrey Williams (12/7/2008)
Instead of trying to fiddle with IGNORE_DUP_KEYS on the index, ...
No "fiddling" required... it's set it and forget it. 😉
--Jeff Moden
December 9, 2008 at 11:11 pm
Jeff Moden (12/9/2008)
Jeffrey Williams (12/7/2008)
Instead of trying to fiddle with IGNORE_DUP_KEYS on the index, ...No "fiddling" required... it's set it and forget it. 😉
Well, that is true - but to be honest that just does seem to be right to me. For this type of process, I would definitely prefer recalculating and loading the data again to make sure I have the most current data - instead of possibly throwing away current information because it was previously saved.
But then again - that's just me 😉
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply