Read from file - put only new data into SQL table

Question

0.00/5 (No votes)

See more:

Hi everyone,

I have a file that continually are added records. I create an application that, 2 or 3 times in a day, take the file, read with a TextFieldParser and put data into a SQL Table.

My problem is: the first time, all data are insert into SQL Table. The second time I have need that only new data are insert.

Which is the best way to do this?

Best Regards

What I have tried:

I created a temporary table to my original and tried using NOT IN statment to have only data that are not insert. But, I have to use the control on a TEXT variable, and from what I understand, NOT IN does not work.

Posted 10-Apr-17 21:30pm

Member 10558090

Updated 10-Apr-17 21:57pm

Add a Solution

Comments

Tomas Takac 11-Apr-17 3:41am

You approach is about right. Loading data into a staging table then I would use MERGE statement. But the text field is a problem. Alternatively you can store the position in the file when you read it first time, then next time scroll to that position and simply read from there.

Member 10558090 11-Apr-17 3:52am

Alternatively I could do so. Take the last record of the table based on the "Date" fields and "Hour" and start reading from there. It's possible?

Tomas Takac 11-Apr-17 4:29am

Not sure how you are going to read from Day & Hour. Better store the size of the part you've already read and then scroll to that position next time. But this ever growing file is weird if you ask me. Maybe you should just copy it to a different location and import from there then let a new file be created in the original location - problem solved.

Richard Deeming 11-Apr-17 13:10pm

Do you mean you have a column defined as the text type in SQL? That's obsolete, and will be removed in a future version:
ntext, text, and image (Transact-SQL)[^]

Try using varchar(max) instead.

Member 10558090 12-Apr-17 5:55am

You're probably right. This will save me a lot of time. I changed varibile in VarChar
and use this query: INSERT INTO ProvaTimbrature (Data, Ora, Codice_Tessera, Verso, Giustificativo, Tipo_Operazione, Lettore, Cognome, Nome, Causale, Matricola_Dipendente, Campo1, Campo2, Controllo) SELECT Data, Ora, Codice_Tessera, Verso, Giustificativo, Tipo_Operazione, Lettore, Cognome, Nome, Causale, Matricola_Dipendente, Campo1, Campo2, Controllo FROM tmpProvaTimbrature WHERE tmpProvaTimbrature.Controllo NOT IN (SELECT Controllo FROM ProvaTimbrature)
Finally, it works.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Answer 1 · 2017-04-10T21:57:00

Solution 1

You have several options:

Empty the file content after having succesfully read it with your application
Record in your database the last file size and, next time, start reading from such a position
...

Posted 10-Apr-17 21:57pm

CPallini

Comments

Member 10558090 11-Apr-17 4:08am

"Record in your database the last file size and, next time, start reading from such a position"

Which statment need I to use?

CPallini 11-Apr-17 4:27am

It depends on the class you are using for reading the file. For instance, the TextReader class provides just Readline or Readblock methods to move the file pointer.