Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Default

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
Reply With Quote
  #3 (permalink)  
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
Default

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
Reply With Quote
  #4 (permalink)  
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
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.