Member Avatar for elie.karkafy

I NEED TO WRITE A CODE IN VB.NET , THAT LET ME EXPORT DATA FROM ACCESS DATABASE ( THE TABLE ATTACHED AS IMAGE ) AND IMPORT IT INTO SQL SERVER 2008 R2 . **NB : TAKING INTO CONSIDERATION DUPLICATION **

Example : if you see in the image the access database contains 5 rows , if i select the first 3 rows and import it into SQL DATABASE THE NEXT TIME I AM SELECTING I SHOULD CHECK THAT 3 ROWS ALREADY SELECTED SELECT THE LAST 2 ROWS AND THAT MEAN NO DATA DUPLICATION

ANY HELP PLZ ?

Table.png 44.17 KB
  • 3 Contributors
  • 3 Replies
  • 1K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Oxiegen
Member Avatar for hericles

The easiest way would be to use the id column as a unique key in the sql server. Then if it already exists the insert would fail. You'd just need to code around the error so it didn't stop processing. The problem there is that every row in the access database would get pushed to the sql server to see if it would be inserted or not. That gets inefficient as the access database grows.

option 2: extract all IDs out of the sql server first and then match the access database, only inserting a row when !contains is true. Saves on unneeded INSERT queries but could be a terrible solution based how you implemented the !contains check.

or you could drop or truncate the sql server table and simply reimport the whole thing each time.

Or possibly alter the access table so after an import all rows imported have a flag set indicating they were replicated. But that may not be possible and could go wrong easily.

Member Avatar for elie.karkafy

can you help please help writing this querry with the condition of checking each time , any help please ?

Member Avatar for Oxiegen

The best option would be to load both databases into their own dataset. That way, when you do the compare, nothing gets pushed to the sql server and cost time.

Also, using the ID as the comparer is a horrible idea.
You should use a selection of fields for comparison.

Insert Into sql.table1 From access.table1 Where Not sql.field1 = access.field1 And Not sql.field2 = access.field2 (and so on)

When you use datasets, and more specifically datatables, you can use the built-in method .ImportRow().

Dim srcDt As DataTable = srcDs.Tables(0) <-- Your Access Db
Dim trgDt As DataTable = trgDs.Tables(0) <-- Your SQL Db

For i As Integer = 0 To srcDt.Rows.Count - 1
    trgDt.ImportRow(srcDt.Rows(i))
Next

After that you follow up with an iteration to check for, and delete, duplicates.
And finally, submit the target dataset to SQL Db.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.