I wouldn't do it thru a front end application. I would probably import
the data into a temporary table with BCP (or DTS). Then I would run a stored
procedure that did the updates and inserts. You can save some steps by
trying something similar to this:
UPDATE tablexx SET Datetime = GETDATE(), Status = xx WHERE UniqueColumn
INSERT INTO tablexx (xxx,xxx,xxx) VALUES (xxx,xxx,xxx)
This way the update will return 0 if the row doesn't already exist and you
can insert the new one. If it does exist then the update is done and you can
move on to the next row. You can do this by creating a cursor and looping
thru it or depending on your existing and new data you might be able to use
SQL statements between the 2. Mabye something like:
INSERT tablexxx SELECT * FROM newtable where newtable.uniquecolumn
not in (Select uniquecolumn from old table)
> Hi All
> I'm working with an average of 1,5 mil Records. Every month end i get a
> dump from a mainframe system in *.txt Format. This file has to be imported
> into SQL7 The process involves:
> Reading a record from the text file
> Check if the record already exists on the main Table
> If so Update a datetime field and a status field
> If not Add the whole record.
> And then Exit all the recors not Updated or Added
> This runs about 18 hours at this stage when i Use ADO.
> I need to get it down to almost 4-5 hours.
> The problem is the Find methode on the ADO is very slow when the record is
> not on the Main table.
> So I'm Using a query to find the record.
> Basicly my Question is : Is there a faster way to determen if the Record
> Already in The Table or Does it Have to be Added.
> If posible Please help.
> Francois de Wet
> Tel: (011) 476-0691
> Fax: (011) 476-0666
> ABSA Group IT