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. 16 1176
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" <lp@a.com> wrote in message
news:O5**************@TK2MSFTNGP14.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.
> (too many db calls?)
Well... yeah!
"Picho" <SP********@telhai.ac.il> wrote in message
news:u5**************@TK2MSFTNGP12.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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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.
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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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.
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" <lp@a.com> wrote in message
news:O5**************@TK2MSFTNGP14.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.
Well I hope you are aware that the dataAdapter also makes a single call for
each row?
"LP" <lp@a.com> wrote in message
news:OM**************@TK2MSFTNGP09.phx.gbl... (too many db calls?) Well... yeah!
"Picho" <SP********@telhai.ac.il> wrote in message news:u5**************@TK2MSFTNGP12.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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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. > >
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" <lp@a.com> wrote in message
news:O5**************@TK2MSFTNGP14.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,
I think that evaluating and redesigning the class that does the updates
would be the best way.
Just my thought
Cor
"LP" <lp@a.com> wrote in message
news:O5**************@TK2MSFTNGP14.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
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" <lp@a.com> wrote in message
news:O5**************@TK2MSFTNGP14.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.
Nick, Now, in the stored proc, take in the row parameters. Check if the row exists. If not, insert it.
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 Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:wu********************@comcast.com... 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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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.
Nick It's amazing how few people actually attempted to answer your question.
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
Uhh .. how is this answer any different from what's already been said? :-/
"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:wu********************@comcast.com... 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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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.
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.
--
"Cor Ligthert" <no************@planet.nl> wrote in message
news:Ow*************@TK2MSFTNGP12.phx.gbl... Nick
It's amazing how few people actually attempted to answer your question.
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
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.
--
"Sahil Malik" <co*****************@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... Uhh .. how is this answer any different from what's already been said? :-/ "Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message news:wu********************@comcast.com... 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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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. > >
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
> Your answer was quite good. I simply added on. No offense intended.
None taken man !! :-) We all have room to improve, I most certainly do; just
read my blog for all the dirty words I use.
- Sahil Malik http://codebetter.com/blogs/sahil.malik/
"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:sM********************@comcast.com... 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. -- "Sahil Malik" <co*****************@nospam.com> wrote in message news:%2****************@TK2MSFTNGP14.phx.gbl... Uhh .. how is this answer any different from what's already been said?
:-/
"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message news:wu********************@comcast.com... 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" <lp@a.com> wrote in message news:O5**************@TK2MSFTNGP14.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. > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: pratchaya |
last post by:
What the best language code to connect MySQL --> C , C++ , php,VB,Delphi ?
about ---- Speed
---- Stable
---- Security
Can anyone compare C , C++ , php4,php5 ,vb ,delphi to connect to...
|
by: Cedric Robinson via .NET 247 |
last post by:
(Type your message here)
--------------------------------
From: Cedric Robinson
Hi, I am simply trying to insert records into an access database using asp.net. I can edit and update tables all...
|
by: Andrew Bourne |
last post by:
I would like to insert multiple records into a table
based on a query returned from the same table. For
example a datareader returns 3 records containing the
following records
user1 file1...
|
by: Rocael Hernandez |
last post by:
Hello all,
I was wondering what's best for PG, since we have to
decide what will be the new server for our DB, that
will serve one or more websites.
Our actual configuration is a dual xeon...
|
by: sparty1022 |
last post by:
we are constructing a (sql 2005) database on an FTP server where we will need
to insert records into the tables. At this point our ftp scripts will allow
us to upload/download documents/file from...
|
by: TerryStone |
last post by:
I am writing a Windows application, with an SQL Server (Express)
database. The application is single user. The Windows application is
the only application that accesses the database.
I have...
|
by: Dave |
last post by:
I'm writing a c# Windows program that needs to be able to insert records
into a dbase table. I can read it using a dataset but can't insert records. I
receive the following error,
ERROR ...
|
by: .Net Sports |
last post by:
I'm trying to insert records into an sql database coming from a page
using the request
..form method. The table "general" has a primary key 'geid .' I get the
following error:
Cannot insert the...
|
by: pamela fluente |
last post by:
I need an opinion. I have to add NEW pages to an existing site.
The site has - also - some ASPX pages. These pages, I have seen, have
the following doctype:
<!DOCTYPE html PUBLIC "-//W3C//DTD...
|
by: Amar Agundu |
last post by:
Hello, I am developing webapplication in JSP.I have a resultset fetched from database.This contains records with many columns but I want to display them on a page in a neat manner.What is best way to...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |