[ Resolved! ] Inserting when value doesn't exist? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[ Resolved! ] Inserting when value doesn't exist?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-08-02 : 10:21:45

Hey everyone, I really hope this is an easy one. I hope I'm just missing something small. I inherited this d/b from somebody that worked here before me and one of the stored procedures he wrote isn't working. Basically, we need to do an insert when a field doesn't already exist.

Here are the tables:

tblCompany
compID (p/k)(integer)
compName (v/c 50)
compAddress (v/c 75)
...

tblEmployee
empID (p/k) (int)
compID (f/k) (int)
empFName (v/c 35)
empLName (v/c 35)
empGroup (v/c 3)
empNumber (v/c 4)
...

tblLastStation
lsEmpNum (p/k) (v/c 7) --(based on empGroup + empNumber) <- I know, I know ... but it's not my design
lsStatID (int) (based on another table tblStation)
lsDateTime (datetime)
...

Basically, whenever an employee of a particular company logs onto a station, their record in the tblLastStation table is updated. No problem. That part is working. The problem occurs when a "global" (well, company wide) update is made). They might be adding 10 new employees at one time. Or they might be changing the "director" for 5 out of 35 employees). The way the person before me wrote it is that whenever one of those types of changes are made, the system will "check" to make sure that each employee is listed in the tblLastStation table. If they are not there, it will insert them with a "temp" station (ID = 1) and current datetime stamp. That's where the problem is happening. I've tried fixing the stored procedure but am hitting a wall. Here is what we have:


CREATE PROCEDURE admin_sp_InsertMultipleLastStationRecords

(

     @in_intCompID INTEGER,
     @in_intLastStatID INTEGER
     
)

AS

SET NOCOUNT ON

BEGIN

     SET IDENTITY_INSERT [tblLastStation] ON

     INSERT INTO tblLastStation
            (
                 lsEmpNum, lsStatID, lsDateTime
            )

            SELECT empGroup + '' + empNumber AS [empNum],
                   lsStatID = @in_intLastStatID, lsDateTime = Now()
            FROM   tblEmployee
            WHERE  compID = @in_intCompID AND
                empGroup +'' + empNumber
                NOT IN (SELECT lsEmpNum FROM tblLastStation)

    SET IDENTITY_INSERT [tblLastStation] OFF

END

I get the error:

quote:
Violation of PRIMARY KEY constraint 'PK_tblLastStation'. Cannot insert duplicate key in object 'tblLastStation'.
The statement has been terminated.

I thought that the last part of my stored proc (WHERE ... NOT IN (SELECT lsEmpNum from tblLastStation) would have handled that. But apparently it's not recognizing that it's already in there.

Can anyone point out what I'm missing? I know I should probably redo the entire logic of this database but at this point it's not really a possibility. So until we attempt to roll out a new version next year I have to make this one work.

Any idea's?

DTFan
Ever-hopeful programmer-in-training

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-02 : 11:28:53

r u sure u posted the same code that u have!!
reason is:
>> lsDateTime = Now()
U cannot have Now() in SQL Server

So may be u have a space in between the quotes, when comparing with "IN"

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 11:31:26

It is the last part that creates the misbehaviour.

WHERE ... empGroup +'' + empNumber NOT IN (SELECT lsEmpNum FROM tblLastStation

EmpGroup can't be found in tblLastStation.

Peter Larsson
Helsingborg, Sweden

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-08-02 : 12:15:45

I apologize for the confusion on the Now(). I've been taking the code and trying to run it through a regular SQL statement (versus stored proc) from VB to figure out what is going wrong. The actual code in my stored proc is probably 70 or 80 lines long with almost everything commented out. Ditto for my VB code.

I am trying to avoid doing a loop or calling the same sp for every record. How else would I go about indicating that the record needs to combine those two fields from the tblEmployee table and to search for that (the combined field) in the tblLastStation table?

With the exception of redoing the d/b and table structure, is there a better approach?

Thanks again for any and all help that you can give. It is very much appreciated.

DTFan
Ever-hopeful programmer-in-training

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 13:57:31

Your code should work fine, as long there is no NULLs in the Employee table.

CREATE PROCEDURE admin_sp_InsertMultipleLastStationRecords
(
     @in_intCompID INT,
     @in_intLastStatID INT
)
AS

SET NOCOUNT ON

INSERT	tblLastStation
	(
		lsEmpNum,
		lsStatID,
		lsDateTime
	)
SELECT	empGroup + empNumber,
	@in_intLastStatID,
	GETDATE()
FROM	tblEmployee
WHERE	compID = @in_intCompID
	AND ISNULL(empGroup + empNumber, '') NOT IN (SELECT lsEmpNum FROM tblLastStation)

Peter Larsson
Helsingborg, Sweden

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-08-03 : 08:11:40

Thanks for the suggestion. I tried that but I'm still getting the same error. It is still saying:

quote:
Violation of PRIMARY KEY constraint 'PK_tblLastStation'. Cannot insert duplicate key in object 'tblLastStation'.
The statement has been terminated.

I'm not sure why it is doing this. Maybe instead of having it combine the numbers together in the WHERE statement I should assign it a variable name or something in a subquery? I don't know if that is possible but I'm going to keep looking through the forum to see if I can find anything that might make this doable.

Any other suggestions or idea's? Thanks again.

DTFan
Ever-hopeful programmer-in-training

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-03 : 08:28:46

May be the following will be helpful for debugging:

* u may put an error trapping routine to see, the records of the select query (the one after the Insert)
* u can have a print statement to print the Select query and run it seperately to c who the culprit is
* u can use the profiler to find, what insert statement, creates that problem.

Srinika

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-03 : 09:51:02

ok, here is a ton of code, may be crap. Give it a run, see if it is what you were after.


create table tblCompany (compID int identity(1,1) primary key, compName varchar(50)
	,compAddress varchar(75))

create table tblEmployee (empID int identity(1,1)primary key,compID int
	,empFName varchar(35),empLName varchar(35)
	,empGroup varchar(3),empNumber varchar(4))

create table tblLastStation (lsEmpNum varchar(7) primary key
	,lsStatID int, lsDateTime datetime)

insert into tblcompany (compName,compAddress)
select 'Freddy Store','123 main'
union all select 'Jane Town','99 state street'
union all select 'Big Buy', '1 oak lane'

insert into tblemployee (compID,empFName,empLName,empGroup,empNumber)
select 1,'Jimmy','Smith','001','0002'
union all select 1,'Sally','Jones','001','0021'
union all select 2,'Bill','Williams','002','0030'
union all select 3,'Tim', 'Brak','003','0100'

insert into tblLastStation (lsEmpNum,lsStatID,lsDateTime)
select '0010002','1',Getdate()
union all select '0010003','2',getdate()

CREATE PROCEDURE admin_sp_InsertMultipleLastStationRecords
(
     @in_intCompID INTEGER,
     @in_intLastStatID INTEGER   
)

AS

SET NOCOUNT ON

INSERT INTO tblLastStation
	(lsEmpNum, lsStatID, lsDateTime)
SELECT 
	e.empGroup + e.empNumber AS lsEmpNum
	,@in_intLastStatID AS lsStatID, GetDate() AS lsDateTime
FROM   
	tblEmployee e
LEFT JOIN
	tblLastStation s
ON
	s.lsEmpNum = e.empGroup + e.empNumber
WHERE  
	e.compID = @in_intCompID 
AND
	s.lsEmpNum is null


-- all done

drop table tblCompany
drop table tblEmployee
drop table tblLastStation

I get the feeling i am missing something, but i am SURE one of the brains will spot it

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-03 : 13:42:45

DTFan,

Could changing from


WHERE ... empGroup +'' + empNumber NOT IN (SELECT lsEmpNum FROM tblLastStation)

to


WHERE NOT EXISTS(SELECT lsEmpNum FROM tblLastStation WHERE lsEmpNum = empGroup + '' + empNumber)

work?

Ken

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-04 : 03:54:27

Something a little on the side: The equivalent for Now() is GETDATE()

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-08-07 : 09:36:35

Good news and bad news (well, bad news kinda for me). The good news is that the problem was solved. I used the existing SQL and substituted KenW's code. It worked. So this part:


WHERE NOT EXISTS
    (
        SELECT   lsEmpNum 
        FROM     tblLastStation 
        WHERE    lsEmpNum = empGroup + '' + empNumber
    )

did get the results that I wanted. However, the other code suggested might also work because after I got the stored procedure to work I saw that it wasn't working for all companies. Basically, it would work half the time.

The bad news is that this is a result of what I was left with from the original database person. Somewhere along the way one of their sp's (and there are 800+ of them) hasn't allowed bad entries into the system. Instead of an employee having a 3 digit empGroup and and 4 digit empNumber, for some reason there are quite a few that have a 3 digit empGroup and a '0' for the empNumber. That was causing problems. When I ran the above code (with the updated WHERE clause) on a company that didn't have any '0' for empNumber, then it ran fine. When I ran it on one that had the '0', I got the same error.

Now I have to start going through all the stored procs, etc and finding where the error is happening. In the meantime, I included this:


WHERE   compID = @in_intCompID
AND     ((empNumber <> '0') AND (empNumber <> '')) 
AND NOT EXISTS
    (
        SELECT   lsEmpNum 
        FROM     tblLastStation 
        WHERE    lsEmpNum = empGroup + '' + empNumber
    )

This appears to be working and should buy me the time to find where the bad entries are coming from.

THANK YOU to everyone for the help and suggestions. It is definitly a relief having that off my plate on this bright and sunny Monday morning. All of the help and pointers is greatly appreciated.

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources