check record exists - SQL Server Forums

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

Transact-SQL (2000)

check record exists

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2006-09-18 : 15:06:23

Hi, I want to INSERT records into employment
WHERE the employer_id/employee_id combination does not already exist. ANy help would be appreciated. Thanks

INSERT INTO employment(employer_id, employee_id, status_id,imported_by)
SELECT (SELECT id FROM employers WHERE sport_id=1 AND employer=menssoccer.client) as employer_id
,(SELECT id FROM employees WHERE tax_id=imports.menssoccer.ssn AND SSN Is Not Null) as employee_id
,1
,'test'
FROM imports menssoccer
WHERE SSN Is Not Null

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-09-18 : 17:02:27

If not exists(select * from employment where employer_id = xxx)
Begin
Insert into Employer(col1...10) select col1 ...col10 from ...
End

With Regards
BSR

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-18 : 17:06:12

How about a unique constraint/index on Employer_ID and Employee_ID?

Harry C
Posting Yak Master

148 Posts

Posted - 2006-09-18 : 17:06:21

ok its not thw easy...

select * from employment where employer_id = xxx AND employee_id = yyy

xxx and yyy values come from the two sub selects in the INSERT

(SELECT id FROM employers WHERE sport_id=1 AND employer=menssoccer.client) as employer_id
,(SELECT id FROM employees WHERE tax_id=imports.menssoccer.ssn AND SSN Is Not Null) as employee_id

So, again, how would I figure out which of these records exist before I try to insert them...Thanks

Harry C
Posting Yak Master

148 Posts

Posted - 2006-09-18 : 17:12:20

quote:
Originally posted by JoeNak

How about a unique constraint/index on Employer_ID and Employee_ID?

There is a unique restraint on it. The problem is that the SQL is wrapped in a transaction and aborts when it hits a record that wont insert. So, I need to determine the offending record before I do the INSERT...

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-18 : 17:23:32

INSERT INTO employment(employer_id, employee_id, status_id,imported_by)
Select
A.Employer_ID
, A.Employee_ID
, 1
, 'test'
From
(SELECT
(SELECT id FROM employers WHERE sport_id=1 AND employer=menssoccer.client) as employer_id
, (SELECT id FROM employees WHERE tax_id=imports.menssoccer.ssn AND SSN Is Not Null) as employee_id
FROM imports.menssoccer
WHERE SSN Is Not Null) A
Left Outer Join
(Select Distinct employer_id, employee_id
From Employment) E On A.Employer_id = E.Employer_ID
And A.Employee_ID = E.Employee_ID
And (E.Employer_ID Is Null Or E.Employee_ID Is Null)

Harry C
Posting Yak Master

148 Posts

Posted - 2006-09-18 : 17:50:56

Thanks Joe, but it still gives me the same unique constraint error...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-18 : 19:48:53

I think Joes's final condition should be AND not or, ie. the joined record does not exist at all, although if you have nulls there you're heading for trouble joining to it.
You could also do something like
WHERE
.....
(select count(*) from Employment a where A.Employer_id = E.Employer_ID and A.Employee_ID = E.Employee_ID)=0

which might be a bit slow.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-18 : 22:27:53

Sorry...

INSERT INTO employment(employer_id, employee_id, status_id,imported_by)
Select
A.Employer_ID
, A.Employee_ID
, 1
, 'test'
From
(SELECT
(SELECT id FROM employers WHERE sport_id=1 AND employer=menssoccer.client) as employer_id
, (SELECT id FROM employees WHERE tax_id=imports.menssoccer.ssn AND SSN Is Not Null) as employee_id
FROM imports.menssoccer
WHERE SSN Is Not Null) A
Left Outer Join
(Select Distinct employer_id, employee_id
From Employment) E On A.Employer_id = E.Employer_ID
And A.Employee_ID = E.Employee_ID
Where (E.Employer_ID Is Null Or E.Employee_ID Is Null)

Harry C
Posting Yak Master

148 Posts

Posted - 2006-09-18 : 22:47:29

Thanks Joe, and everyone else who responded, I appreciate it.

Kristen
Test

22859 Posts

Posted - 2006-09-19 : 01:24:44

Can't it be done (more readably IMHO) with:


SELECT	employee_id.Employer_ID,
	employee_id.Employee_ID,
	1,
	'test'
FROM	imports.menssoccer
	JOIN employers AS employer_id
		 ON employer_id.sport_id = 1 
		AND employer_id.employer = menssoccer.client
	JOIN employees AS employee_id
		 ON employee_id.tax_id = imports.menssoccer.ssn 
		AND employee_id.SSN IS NOT NULL
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	Employment AS E
		WHERE	    E.Employer_ID = employee_id.Employer_id
			AND E.Employee_ID = employee_id.Employee_ID
	)

or

SELECT	employee_id.Employer_ID,
	employee_id.Employee_ID,
	1,
	'test'
FROM	imports.menssoccer
	JOIN employers AS employer_id
		 ON employer_id.sport_id = 1 
		AND employer_id.employer = menssoccer.client
	JOIN employees AS employee_id
		 ON employee_id.tax_id = imports.menssoccer.ssn 
		AND employee_id.SSN IS NOT NULL
	LEFT OUTER JOIN Employment AS E
		 ON E.Employer_ID = employee_id.Employer_id
		AND E.Employee_ID = employee_id.Employee_ID
WHERE	    E.Employer_ID IS NULL
	AND E.Employee_ID IS NULL

adding a DISTINCT if there are multiple hits between menssoccer, employers and employees

Edit: I decided I prefered the OUTER JOIN instead of the EXISTS after all!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources