Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 29th, 2004, 11:45 PM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default Keep from inserting duplicates based on 2 fields

    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
Old September 30th, 2004, 02:15 AM
Friend of Wrox
Join Date: Jun 2004
Location: Chennai, Tamil Nadu, India.
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76

You create a Unique Index on these three fields


Old October 1st, 2004, 12:10 AM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts

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?


--- Tom
Old October 1st, 2004, 07:19 AM
Friend of Wrox
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma

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

                   (Select (COL1+COL2+COL3) from TBL_INITIAL
                    Group By (COL1+COL2+COL3) Having

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 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

Similar Threads
Thread Thread Starter Forum Replies Last Post
inserting fields in a related database abhi_loveu2002 ASP.NET 1.0 and 1.1 Basics 3 December 4th, 2006 09:02 AM
Yes to duplicates MMJiggy62 Beginning VB 6 3 July 14th, 2006 11:51 AM
Using Forum fields select fields on the fly hellosureshkumar Crystal Reports 0 December 17th, 2004 08:20 AM
Inserting forms fields into Access tsparker Classic ASP Databases 1 October 9th, 2003 03:24 AM
Duplicates ashley_y Access 11 August 14th, 2003 03:41 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.