Vb.Net - ExecuteNonQuery Duplicate

Posted on 2014-07-10
Last Modified: 2014-07-16
Good Day Experts!

I have a Vb.Net application that I am trying to improve the processing speed.  It takes too long to finish when many records in the textfile.

Currently I query the database checking for the keyed value before doing the insert if no duplicate. If I empty the database and take off the Duplicate check it is waaaay faster.

Is it possible to just execute the NonQuery insert...does the system return a distinct error message if keyed value found which I can check for and then bypass to the next record?

Thanks for your ideas,
Question by:Jimbo99999
    LVL 32

    Assisted Solution

    Do you need a message for each row? How big are those files?

    Normally I would import the entire file into a staging table. Then you can use
    INSERT INTO DestinationTable
    SELECT ColumnList
    FROM StagingTable S
    INNER JOIN DestinationTable D ON D.PrimaryKeyColumns = S.PrimaryKeyColumns
    WHERE D.APrimaryKeyColumn IS NULL;

    Open in new window

    This will copy only the new rows. When your duplicate check involves more than the primary key columns, then include them in the JOIN condition.
    LVL 83

    Accepted Solution

    You can check for duplicates and insert the row within a same query using something like below

    If Not Exists (Select ColumnName From TableName Where ColumnName=Value)
       Insert Into ...

    Author Closing Comment

    I awarded both contributors with the points since my question was abandoned...we are understaffed and I did not get back to this project in time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now