Append & Exists

  • Thread starter Thread starter Johnny Emde
  • Start date Start date
J

Johnny Emde

Hej All

I've created an Append Query with params:
INSERT INTO tblDepartments ( DepartmentName, Created, Modified )
SELECT [p_DepartmentName] AS Expr1, Now() AS Expr2, [Expr2] AS Expr3;

I want to du something like this, to awoid creating two equels records:

IF NOT EXISTS ( SELECT * FROM tblDepartments WHERE DepartmentName =
[p_DepartmentName] )
Then do the append.

I that possible within the Query?

Kind regards
Johnny Emde Jensen
 
Hi,



With Jet? create an index not allowing duplicated values and blindly append
the data, and ignore the error about records not being appended due to
duplicated value it would have occurred. With Jet, by default, records that
are acceptable are appended, even if some records are not appended (due to
violation of constraint or otherwise). In MS SQL Server, the whole batch
would be rollback, by default, as soon as one record "fail" on one
constraint, but NOT in Jet.


Alternatively, add a WHERE clause in your SELECT:


INSERT ... SELECT ... WHERE p_DepartmentName NOT IN (SELECT DepartmentName
FROM tblDepartments)



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top