INSERT from a SELECT where Identity doesn''t match

Question

Post reply

INSERT from a SELECT where Identity doesn''t match

ganiered

SSC Enthusiast

Points: 192
More actions
February 7, 2005 at 12:38 pm

#87586

I am trying to insert a trace log from one table to another as a SQL Server Job every hour. The initial table (tblEmp_TraceLog) gets cleared out every time the trace is restarted so I need to copy everything to a permanent table (tblEmp_Trace) to prevent erasure. tblEmp_TraceLog has its primary key start over at 1 every time but tblEmp_Trace does not. This means that when I try to insert the rows, the RecIDs will be different. I'm looking for a way to possibly increment the ID in tblEmp_Trace on the insert. This is what I tried but it doesn't work:
declare @MaxDate datetime, @MaxRec int
select @MaxDate = MAX(StartTime), @MaxRec = MAX(RowNumber) from tblEmp_Trace
insert into tblEmp_Trace(RowNumber,EventClass,TextData,HostName,ApplicationName,LoginName,SPID,StartTime,Reads,Reviewed)
select RowNumber = (@MaxRec + 1), EventClass, TextData, HostName, ApplicationName, LoginName, SPID, StartTime, Reads, Reviewed = 0
from tblEmp_TraceLog where @MaxDate < StartTime
Does anyone have any suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Can you be more specific? What doesn't work? No insert?

Why is @MaxDate less than StartTime?

I wasn't born stupid - I had to study.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Depends on requirements.

Is there a requirement to preserve the original IDENTITY value from each hourly trace ?

If Yes, then you need a 2 column composite key in the history table (ID plus Hour or timestamp).

If No, then omit the ID column completely from the history table - after all, if there's no requirement to preserve it, why jump through hoops finding ways to deal with dupes of it.

ganiered SSC Enthusiast Points: 192 More actions · Answer 3

This is the error message I get:

"Violation of PRIMARY KEY constraint 'PK__tblEmp_Trace__025D5595'. Cannot insert duplicate key in object 'tblEmp_Trace'."

(@MaxRec + 1) doesnt actually increment @MaxRec so it remains the highest record that's already in tblEmp_Trace.

@MaxDate needs to be less than StartTime so records that were already copied over aren't duplicated.

The record IDs from the initial trace table are unimportant because they get reset anyway. The table they are copied to needs to have a RecID though. This is why I'm not doing a straight-up insert of the recID and am trying to increment it on insert instead.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

>>The table they are copied to needs to have a RecID though.

Right, but since it's unrelated to the intial trace table, why not just make it an IDENTITY type and do away with the arithmetic expressions trying to auto-increment yourself ?

ganiered SSC Enthusiast Points: 192 More actions · Answer 5

OK, I solved this problem. It was something completely stupid and unrelated (as usual). The TextData columns had different sizes so it didnt want to insert. Once I changed that it allowed the identity column to go up by itself and I didn't have to specify it in the query:

declare @MaxDate datetime

select @MaxDate = MAX(StartTime) from tblEmp_Trace

insert into tblEmp_Trace(EventClass,TextData,HostName,ApplicationName,LoginName,SPID,StartTime,Reads)

select EventClass, TextData, HostName, ApplicationName, LoginName, SPID, StartTime, Reads from tblEmp_TraceLog where @MaxDate < StartTime

Thanks for the help though!