general Q about formulae

You don't mention how you import the source data (from a flat file,
from another table etc.), but assuming that it's in a table - or you
can put it there - then you can INSERT only rows which don't already
exist:

insert into dbo.TargetTable (col1, col2, ...)
select col1, col2, ...
from dbo.SourceTable s
where not exists (
select *
from dbo.TargetTable t
where s.SampleID = t.SampleID
and s.TestName = t.TestName)

This is assuming that (SampleID, TestName) is the primary key of the
target table, which seems to be what you've described.

When you INSERT a lot of rows together, it's usually all or nothing -
if one row violates a unique constraint, the whole INSERT is rolled
back. There can be exceptions to this if you're using bulk import tools
like bcp.exe or DTS which are able to send the 'problem' rows to a
separate file.

Simon

(si**********@kingshc.nhs.uk) writes:

Before I go into specifics, this is my problem. I've have a table
that gets updated with large amounts of data on a monthly basis.
Sometimes (rarely) identical rows of data are on one months import that
already exist from the previous month. I can identify these rows from a
combination of two fields (sampleID and testname).
My question is this. Would it be an appropriate 'fix' if I created a
new 'formula' field on the table comprising of a concatentation of
these two fields and then made that an index field (no duplicates)?
Better yet is just to create a unique index on (sampleID, testname). No
need for formulas.
My guess would be that if we then tried to import a record with a
sampleID and testname that already existed, then the import for that
record would simply fail.

Depends a little on how the import is implemented. But normally that
means that all fails, not just the duplicate. However, there is the
option IGNORE_DUP_KEY for the CREATE INDEX statement. If you add this
option, you will get the behaviour you are looking for.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On 3 Aug 2005 06:42:46 -0700, si**********@kingshc.nhs.uk wrote:

Before I go into specifics, this is my problem. I've have a table
that gets updated with large amounts of data on a monthly basis.
Sometimes (rarely) identical rows of data are on one months import that
already exist from the previous month. I can identify these rows from a
combination of two fields (sampleID and testname).
My question is this. Would it be an appropriate 'fix' if I created a
new 'formula' field on the table comprising of a concatentation of
these two fields and then made that an index field (no duplicates)? My
guess would be that if we then tried to import a record with a sampleID
and testname that already existed, then the import for that record
would simply fail.
Would this work? Is there a better way? My background is more with
Access so apologies if I'm not using the right terminology.

Hi Simon,

Yes, this might work - though (as Simon Hayes already pointed out) it
depends on the used method to import the data if it would refuse only
the duplicated row(s) or roll back the entire operation.

However, there is no need to add this 'formula' column. You can just as
easy define a PRIMARY KEY or UNIQUE constraint on a combination of two
(or more) columns. So in your case, the syntax might be something like:

ALTER TABLE MyTable
ADD CONSTRAINT NoDuplicates UNIQUE (sampleID, testname)

I would definitely add this constraint, if I were yoou. But I would not
rely on the database's error detection and handlig to prevent the
duplicates. I think of constraints as a safety belt - a good driver
makes sure it's never needed; the belt is only there because even the
best driver sometimes makes a mistake. But you don't go crashing into a
brick wall on purpose, because the safety belt will keep you safe.

So in short:
- Incorporate Simon Hayes' suggestion into your code to prevent
insertion of duplicates;
- Add a UNIQUE or PRIMARY KLEY constraint on (sampleID, testname) to
make sure that duplicates will enver be entered by any means;
- Don't add an extra column to store the concatenation of sampleID +
testname, since you don't need it for this purpose.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

All,
Thanks for everyones replies. Sadly, (or happily depending on your
viewpoint!) I am not a full-time DBA so I haven't had a chance to implement
your suggestions. Just for info, the import comes from an Access DB
connected through ODBC running automatically, along with several other
export steps, in a DTS package that was created for us by someone who knew
what they were doing who has since moved on. Typical, I know!
If I get any more probs, I know where to come - thanks again!

Simon

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:on********************************@4ax.com...

On 3 Aug 2005 06:42:46 -0700, si**********@kingshc.nhs.uk wrote:

Before I go into specifics, this is my problem. I've have a table
that gets updated with large amounts of data on a monthly basis.
Sometimes (rarely) identical rows of data are on one months import that
already exist from the previous month. I can identify these rows from a
combination of two fields (sampleID and testname).
My question is this. Would it be an appropriate 'fix' if I created a
new 'formula' field on the table comprising of a concatentation of
these two fields and then made that an index field (no duplicates)? My
guess would be that if we then tried to import a record with a sampleID
and testname that already existed, then the import for that record
would simply fail.
Would this work? Is there a better way? My background is more with
Access so apologies if I'm not using the right terminology.

Hi Simon,

Yes, this might work - though (as Simon Hayes already pointed out) it
depends on the used method to import the data if it would refuse only
the duplicated row(s) or roll back the entire operation.

However, there is no need to add this 'formula' column. You can just as
easy define a PRIMARY KEY or UNIQUE constraint on a combination of two
(or more) columns. So in your case, the syntax might be something like:

ALTER TABLE MyTable
ADD CONSTRAINT NoDuplicates UNIQUE (sampleID, testname)

I would definitely add this constraint, if I were yoou. But I would not
rely on the database's error detection and handlig to prevent the
duplicates. I think of constraints as a safety belt - a good driver
makes sure it's never needed; the belt is only there because even the
best driver sometimes makes a mistake. But you don't go crashing into a
brick wall on purpose, because the safety belt will keep you safe.

So in short:
- Incorporate Simon Hayes' suggestion into your code to prevent
insertion of duplicates;
- Add a UNIQUE or PRIMARY KLEY constraint on (sampleID, testname) to
make sure that duplicates will enver be entered by any means;
- Don't add an extra column to store the concatenation of sampleID +
testname, since you don't need it for this purpose.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)