is there a way in SQL Server 2000 that I can only append records where
the information that I'm trying to append doesn't violate primary key
restrictions? In MS Access, when I run the append query, it will throw
up an error message saying that it can't append X number of records
due to violations but will allow me to append whatever didn't have the
violation...in SQL Server, it stops the entire T-SQL statement, not
adding any records whatsoever.

for example, let's say that I have a table that shows FirstNames as
well as other names that is also used instead of this name:

FieldNames: Name:Alias
Bill:Billy
Bill:Will
Bill:William
 
The combined field values of [Name] & [Alias] are the primary key, as
I don't want duplicates of the combination. So if I want to add the
following field values:

Bill:Willy
Bill:Will

I want to be able to add the first set (not already listed) and to
simply ignore the attempt of adding the second set (already in the
table).

---

Request for Question Clarification by mathtalk-ga on 21 Jul 2004 21:13 PDT

Hi, pittsburghsteelers-ga:

There is an option when creating a unique index that allows for
inserts (appends in the MS Access vocabulary) to selectively accept
only those new rows which do not violate the unique index constraint.

However your Question asks specifically about "primary key
restrictions".  Although every primary key definition implicitly adds
a unique index, the primary key is a bit more restrictive than just a
unique index on the same columns.

In particular a primary key will not allow Null values in any of the
columns that make up the key (but a unique index will, subject to
satisfying uniqueness).

Would you like to know more about the option for working with unique indexes?

regards, mathtalk-ga

---

Clarification of Question by pittsburghsteelers-ga on 21 Jul 2004 21:33 PDT

Regarding primary key versus unique index, the two fields are the
primary index (and also unique) because they uniquely identify just
this one individual record. So my question is...is there a way to do
the same thing on the primary key as for just a unique index? Thanks.

---

Request for Question Clarification by mathtalk-ga on 22 Jul 2004 08:26 PDT

Just to clarify, it is technically possible to have the primary key
together with a unique index that supports the sort of "permissive"
attempt to insert rows that may or may not violate the uniqueness
constraint.  However this is overly complicated, if what you really
want is the functionality described.

If you don't have ownership permissions on the table (and thus cannot
change its DDL), then you are stuck with the table the way it is.

If you do have ownership permissions, then the simplest approach is to
replace the primary key constraint with a more appropriate unique
index.  Functionally it would do everything the same, except by
allowing the batch inserts to proceed.

If you really want to have a primary key, then you would need to have
both that and the unique index (a belt and suspenders approach). 
There are some pitfalls that have to be avoided along this path, but
it can be done.

regards, mathtalk-ga

are you doing a record by record insert or are you doing a bulk insert
select thingie?

I'm attempting to do this insert as an automated "bulk" task, not
record-by-record. Thanks.

Hi pittsburghsteelers,

Simple answer: row by row insert. Microsoft SQL Server handles
transactions in a very different way MS Access does. Every transaction
has to satisfy the Object Management Group Transaction Service
specifications. A transaction should be atomic, with consisted
results, cpu isolated and durable.

If a constraint is violated, SQL Server reverses the whole transaction
and no rows are inserted/updated. These operations are labeled
"atomic", because you cannot split them, either the whole bulk
transaction succedes with no errors, or the whole transaction fails
and its reversed. This is done in a way to protect the integrity and
consistency of the data.

Imagine that you are about to receive money from an ATM when the power
grid goes down. The money deduction from your account was completed
with the bank, but then the whole transaction is reversed because it
failed at the last moment.

Import the data into a temp table.  Create a query that joins the
"new" table to the existing table with a left join.  Make the
selection criteria -
Where YourExistingTable.PrimaryKey Is Null.  This query will return
all rows from the "new" table that do not have key matches in the
existing table.  Then change this into an append query.