Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

INSERTED DUPLICATE RECORDS Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2007 11:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 22, 2007 12:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 3, 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
Posted Thursday, November 22, 2007 5:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 22, 2007 5:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:52 AM
Points: 1,326, Visits: 1,288
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
Posted Thursday, November 22, 2007 6:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 22, 2007 6:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:52 AM
Points: 1,326, Visits: 1,288
hi,
you can also schedule the dts to run automatically each monday.
Post #425097
Posted Thursday, November 22, 2007 7:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425100
Posted Thursday, November 22, 2007 7:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, November 22, 2007 7:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12
The vb program will split the strings and remove unnecessary values such as 0's, and converts strings to datetime.
Post #425104
Posted Thursday, November 22, 2007 7:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12
There are no joins in my expressions, there is a unique index(ID).
Post #425105
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse