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

x
  • 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,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
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.
0
 
CodeCruiserCommented:
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)
Begin
   Insert Into ...
End
0
 
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.
0

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