INSERTED DUPLICATE RECORDS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » INSERTED DUPLICATE RECORDS

22 posts, Page 1 of 3

1 2 3 »»»

INSERTED DUPLICATE RECORDS

Rate Topic

Display Mode

Topic Options

Author

Message

Ramoncito Medina

Posted Tuesday, November 20, 2007 11:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12

I'm running a vb program that will insert data to my database from a text file for report generation. It will insert if the record does not exist. It has a validation that if you try to upload two or more same records, the program will not allow it. It runs weekly after extraction of records from another system. The problem is, when the report was generated for the month of October, each record has its duplicate. I checked the text file where the record came from, but it does not have any single duplicates. I test again the vb program, but the validation is working and does not allowed the records to be uploaded to the database. What would be the probable cause of the duplication of the records?

Post #424427

Joe Clifford

Posted Thursday, November 22, 2007 12:53 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 03, 2013 9:53 PM
Points: 433, Visits: 619

A couple of questions:

(1) How are you enforcing the "no duplicates" rule? In the database via a constraint (e.g. unique index) or in your VB application?

(2) Are you joining the data inserted to another table in a query? Any chance you've got a duplicate in a joined table?

Joe

Post #425058

Ramoncito Medina

Posted Thursday, November 22, 2007 5:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12

I enforce the "no duplicates" via my vb program and it is working.

It's not joined, the data are directly inserted in the table from a text file via my vb program.

Post #425083

SQL_ABD

Posted Thursday, November 22, 2007 5:34 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi,
why you dont use a DTS/SISS or BCP to insert the data from your text file to the database.

http://www.databasejournal.com/features/mssql/article.php/3391761
http://www.verio.com/support/documents/view_article.cfm?doc_id=3846
http://www.sqlis.com/
Regards,
Ahmed

Post #425086

Ramoncito Medina

Posted Thursday, November 22, 2007 6:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12

I can't, because the vb program is automatically running to lessen the effort, meaning, it is assigned as a job that will run every Monday after the extracted text file has been imported to its source folder instead of manually doing the DTS.

Post #425094

SQL_ABD

Posted Thursday, November 22, 2007 6:57 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

hi,
you can also schedule the dts to run automatically each monday.

Post #425097

Jeff Moden

Posted Thursday, November 22, 2007 7:01 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

Who said anything about a "manual process"? Schedule it! BCP, a proc that uses Bulk Insert, and DTS can all be scheduled.

Does your table have a valid, non-surrogate Primary Key? If not, you need to figure out how to make one.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #425100

Dave I

Posted Thursday, November 22, 2007 7:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:26 PM
Points: 114, Visits: 477

All true.

To answer his question, though, if:
A. Data is only transfered to the database via the program and
B. Logic of the program is bullet proof and
C. There are no duplicates in the database (Rules out an extra call to the program with the same file OR the same data appearing in a different file) and
D. The report WAS showing duplicates

Then:
A. The design of the report is bad (Unlikely to see duplicates) or
B. The query the report is based off of is bad (More likely. Double-check your joins.) or
C. Design flaw in the reporting tool (Get another set of eyes to look things over, if possible. If it's reproducible, you may want to send a bug report and work around it.)

Post #425103