SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERTED DUPLICATE RECORDS


INSERTED DUPLICATE RECORDS

Author
Message
Ramoncito Medina
Ramoncito Medina
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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?
Joe Clifford
Joe Clifford
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2199 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



Ramoncito Medina
Ramoncito Medina
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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.
SQL_ABD
SQL_ABD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 1349
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
Ramoncito Medina
Ramoncito Medina
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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.
SQL_ABD
SQL_ABD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 1349
hi,
you can also schedule the dts to run automatically each monday.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218233 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DaveItz
DaveItz
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 483
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.)
Ramoncito Medina
Ramoncito Medina
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 12
The vb program will split the strings and remove unnecessary values such as 0's, and converts strings to datetime.
Ramoncito Medina
Ramoncito Medina
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 12
There are no joins in my expressions, there is a unique index(ID).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search