I am new to SQL Server and am having a problem trying to figure out the best way to approach it.

I am working with SQL Server 2000.

    My database has two tables of interest with fields that are 60% identical (that is, most of the

fields have the same data type and context, although they have different field names). For the purpose

of this inquiry, let me call the first table "tbl_INITIAL" and the second, "tbl_FINAL". Table

"tbl_INITIAL" is a temp table that is used to hold the contents of imported data for some

pre-processing. It is populated by a DTS package that imports a .CSV file. It is preferable that it not

be constrained until all pre-processing is finished.

    "tbl_FINAL" has 10 of its 15 fields that have "identical" counterparts in "tbl_INITIAL". By

identical, I mean they have the same structure and context, but have different names. The remaining 5

fields in "tbl_FINAL" have no bearing on this discussion. "tbl_INITIAL" has records that I want to

"append" to "tbl_FINAL".

    I need to make sure that no record in "tbl_INITIAL" with the "combination" of the following 3

fields, fld_FIRST, fld_SECOND, and fld_THIRD, are duplicated in "tbl_FINAL" during insertion or

modification. It is not important or desired to notify the user when one of these records is prohibited

from being inserted.

I was thinking of 3 approaches, all of which are new areas of learning for me:

1) Write a procedure (view, stored proc, or function???) to insert the records, but check for and

prevent "3-field combination" duplicates from being committed. I don't think this help when a record in

"tbl_FINAL" is modified.

2) Modify the structure of "tbl_FINAL" so that the combination of the 3 fields is UNIQUE. I don't know

what the consequences of this would be during batch updates. Would I get errors that would stop the

process, or would it just prevent the insert and keep going?

3) Build a trigger that would check for this condition on insert or modify and prevent the action if a

duplicate, 3-field combination shows up.

Needless to say, I don't know which approach is best OR how to write the code to do any them.

Can someone suggest which of the 3 or combination of the 3 approaches is the best way to go?

Could you point me to some examples?

Thanks ahead of time! :)






--- Tom

__________________
--- Tom

You create a Unique Index on these three fields

ALTER TABLE tblFinal ADD CONSTRAINT
IX_tblFinal UNIQUE NONCLUSTERED
(
        fld_FIRST,
    fld_SECOND,
    fld_THIRD
)

Regards
Ganesh

Does this work for INSERTS and UPDATES? I will be doing the insert and updates from Access (.mdb) via ADO. I would like to have the procedure return the number of files it rejected based on this CONSTRAINT.

Any ideas?

Thanks,
Tom


--- Tom

If I were u. I would be using two steps.

INSERT INTO TBL_FINAL SELECT * FROM TBL_INITIAL where (COL1+COL2+COL3) NOT IN
                   (Select (COL1+COL2+COL3) from TBL_INITIAL
                    Group By (COL1+COL2+COL3) Having
                    Count(COL1+COL2+COL3))>1
                    )

This will Insert all the valid data quickly.

2) In same batch and Transaction, Create a cursor holding this resultset:-

SELECT *,(COL1+COL2+COL3) FROM TBL_INITIAL where (COL1+COL2+COL3) IN
           (Select (COL1+COL2+COL3) from TBL_INITIAL Group By
             (COL1+COL2+COL3) Having Count(COL1+COL2+COL3))>1
            ) Order by (COL1+COL2+COL3)

Now Insert One by one row from this cursor using T-SQL While loop and each new (COL1+COL2+COL3) will be compared for the eqaulity with previous (COL1+COL2+COL3) and if found same, can be written to atep table, otherwise insert into TBL_FINAL.

If u r not comfortable with T-SQL Scripts and batches or cursors, I suggest u to use ADO recordset sorted on (COL1+COL2+COL3) and in the same way catch the dulicate rows using variables and if duplicate, write to a text file, otherwise insert into TBL_FINAL.

Hope u got the idea. If u r keen to do with T-SQL, it can be done in a single T-SQL Batch, otherwise ADO would be much handy way to do.

Good luck



B. Anant