What's the best way to insert new records only

  • Thread starter Thread starter LP
  • Start date Start date
L

LP

Hi,

Every morning a .NET application downloads a file with cumulative data which
needs to be appended to SQL Server table. This program needs to identify
records that have not been previously inserted (there's a unique identifier
field) and only insert those. Also I must reuse our class that does updates,
it basically can update any table by using SqlDataAdapter .update method. So
I have to rule out bulk inserts, DTS, etc...
I think I have little choice but to load all records first to a "temp" table
and then append only new records (where not exists) to a "real" table. Can
anyone think of a better solution?

Thank you.
 
how about itterating all downloaded records, for each one run an
ExecuteScalar() to see if the unique identifier already exists and if not
insert it? (too many db calls?)

Picho
 
(too many db calls?)
Well... yeah!

Picho said:
how about itterating all downloaded records, for each one run an
ExecuteScalar() to see if the unique identifier already exists and if not
insert it? (too many db calls?)

Picho
Click to expand...
 
Just a thought, Have you looked at sending the data as an XML-document
to for instance a stored procedure that uses the OPENXML statement to
insert data?

Regards,
Joakim
 
Write a BCP/DTS upload routine to get the data on the server. Next, write a
merge routine that executes an INSERT that adds the new rows using a WHERE
clause that has
... newID NOT IN (SELECT ID From YourTargetTable)

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Okay when the file is downloaded, you have all the data you need to work
upon in one shot.

My suggested options are -

Preference #1 - BCP/DTS package exported to VB, converted to VB.NET/C#.NET
using Interop.
Preference #2 - SqlXML used over Interop that ships with MDAC 2.7. Send in
an UpdateGram.

But now when you say that you "must reuse the class that does updates", then
you are already doing the best that can be done given your constraints. i.e.
row by row concurrency checks and insert/updates. The temp table approach
doesn't seem to have substantial benefit IMO.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
LP,

I think that evaluating and redesigning the class that does the updates
would be the best way.

Just my thought

Cor
 
LP said:
Hi,

Every morning a .NET application downloads a file with cumulative data
which
needs to be appended to SQL Server table. This program needs to identify
records that have not been previously inserted (there's a unique
identifier
field) and only insert those. Also I must reuse our class that does
updates,
it basically can update any table by using SqlDataAdapter .update method.
So
I have to rule out bulk inserts, DTS, etc...
I think I have little choice but to load all records first to a "temp"
table
and then append only new records (where not exists) to a "real" table. Can
anyone think of a better solution?

Thank you.
Click to expand...

As others have said....

I'd not use a .NET application in that way unless I really had to.
In other words, if the app does something to manipulate the table which
necessitates user intevention then maybe I'd reconsider.
This is an every day task so I'd schedule a job every day and use DTS.
I'd have an interface table in my database.
The job would delete the old stuff in there, import the file into the table,
run a stored procedure which just appended new entries.
If there was a problem with the process or a suspected problem then i can go
back and look at the data in that table and track it down easy.
I like easy.
 
It's amazing how few people actually attempted to answer your question.

Using a data adapter, you can specify any command you want to occur when a
row is inserted. Specify that command as a stored procedure call in SQL.
(Perfectly legal).

Now, in the stored proc, take in the row parameters. Check if the row
exists. If not, insert it.

It really is that simple.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
Nick,
Now, in the stored proc, take in the row parameters. Check if the row
exists. If not, insert it.
Click to expand...

I think that is the solution I was looking for!
I am really constrained by using our custom updater class. And I am aware
that DataAdapter inserts one record at a time, but it's a batch process that
runs overnight, so who cares.
But the good news is that this custom updater is exremly flexible; so
command is exposed as a property.
Thanks a lot.
 
Nick
It's amazing how few people actually attempted to answer your question.
Click to expand...

In my opinion was this in a kind of same way answered in the first answer in
this messagethread by Picho already as you did.

I had nothing to add to that, and still do I find that and yours not the
best approach.

The same answer as when somebody asks "my car is always out of petrol what
can I do about that" and than the answer is "push the car". Or maybe better
"let your wife/girlfriend push the car"

Cor
 
Hi Cor,

With all due respect, the OP stated that he had NO CHOICE but to use the
custom data adapter component he had. This component may or may not have
been developed by him (it appears that it was not), but it was clear from my
reading that he did not have the option of avoiding it or redesigning his
app. His question was specifically about how to USE that adapter to meet
his needs, not whether his design was any good. Therefore, your answer,
which critiqued his design, did not respond to his actual question.

I enjoyed your humorous analogy.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
 
Hello Sahil,

It was clear to me that the OP was not aware of the fact that he could
manipulate the Insert command in the data adapter. I simply pointed that
out. This allowed him to avoid the temp table design, which is what he
wanted to do. No one else had provided him an insight that would avoid the
temp table.

Your answer was quite good. I simply added on. No offense intended.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
Nick,

After sending my message I was thinking it over, maybe I wrote it to cruel
too you, what was not my intention of course, just discussing the problem. I
hope you understood that from the last rows.

Let me explain it better why I wrote it.

A procedure that first looks if there are new ones, create new ones and than
update, is a procedure old as the ICT and what is the easy way to do. I do
not tell the trouble that can come from that. However, in a tape environment
it did not give any problem except that you had to use one extra tape.

That is solved better by most people and that is as well done right in the
AdoNet environment.
(I know that you know what I write bellow however without it I cannot make
it clear)

In AdoNet with the dataset approach, you fill a dataset. The rows are than
directly set to unchanged (when you not set the parameter not to do that).
After that, you do updates on that dataset where the existing rows get the
status "changed" and the new ones "inserted".

The dataadapter has the intelligence to use for the changed rows the
"update" command in that and for the inserted ones the "insert" command.
That goes in one time and does not do first an insert process and than an
update process.

In my opinion is that a good approach, al was it alone that you do not do
twice a write. When the design not allows that, than you have in my opinion
two possibilities.

"Redesign your design or take the solution from Picho."

The solution from Picho documents direct that there is something special
with the design that needs to be done extra, and than is his simple testing
with execute.scalar and than insert a row for me a very fine approach. Let
say the best of the worst.

When you say, I am able to let the dataadapter do an update and catch that
in the SQL server when that has to be an Insert and change it than as an
Insert when the row does not exist. Than is it maybe a more SQL oriented
solution which I do not know, however as well a bandage that can be used and
I do not like those fixes. (Documentation and using programs in the way as
was supposed)

I hope that I make my thoughts why I wrote it clear with that.

Cor
 
Back
Top