What's the best way to insert new records only

[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.

[Picho]

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
"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

[LP]

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

"Picho" <SP********@tel hai.ac.il> wrote in message
news:u5******** ******@TK2MSFTN GP12.phx.gbl...

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
"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

[Joakim Karlsson]

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

Picho wrote:

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
"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

[William \(Bill\) Vaughn]

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.
_______________ _______________ ____

"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

[Picho]

Well I hope you are aware that the dataAdapter also makes a single call for
each row?
"LP" <[e-mail]> wrote in message
news:OM******** ******@TK2MSFTN GP09.phx.gbl...

(too many db calls?)

Well... yeah!

"Picho" <SP********@tel hai.ac.il> wrote in message
news:u5******** ******@TK2MSFTN GP12.phx.gbl...

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
"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

> 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.
>
>

[Sahil Malik]

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" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

[Cor Ligthert]

LP,

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

Just my thought

Cor

[Andy O'Neill]

"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.

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.

--
Regards,
Andy O'Neill

[Nick Malik [Microsoft]]

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.
--
"LP" <[e-mail]> wrote in message
news:O5******** ******@TK2MSFTN GP14.phx.gbl...

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.