[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

Vb.Net - ExecuteNonQuery Duplicate

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,
2 Solutions
ste5anSenior DeveloperCommented:
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.
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 ...
Jimbo99999Author Commented:
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.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now