Data Import Need Help

Data Import Need Help

Post by Francois de We » Thu, 13 Jul 2000 04:00:00



Hi All

I'm working with an average of 1,5 mil Records. Every month end i get a data
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 is
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

 
 
 

Data Import Need Help

Post by Andrew J. Kell » Thu, 13 Jul 2000 04:00:00


Francois,

    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
= xxxx


        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)

Andy



> Hi All

> I'm working with an average of 1,5 mil Records. Every month end i get a
data
> 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
is
> 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


 
 
 

Data Import Need Help

Post by Evert Timme » Thu, 13 Jul 2000 04:00:00


The UPDATE can be done a bit quicker...

UPDATE tablexx SET tablexx.fieldxx = newtable.fieldxx FROM tablexx INNER
JOIN newtable ON tablexx.UniqueField = newtable.UniqueField



> Francois,

>     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
> = xxxx


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

> Andy



> > Hi All

> > I'm working with an average of 1,5 mil Records. Every month end i get a
> data
> > 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
> is
> > 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

 
 
 

1. Data Import Need Help

Hi All

I'm working with an average of 1,5 mil Records. Every month end i get a data
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 is
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

2. Oracle Data Mart versus Enterprise edition

3. Need to import data to a previous version (blobs not imported)

4. concurrent postgres in NUMA cluster postgres - design OK ?

5. Help needed importing data

6. $$$ VB Developers wanted...

7. Need help importing Excel data

8. Enterprise Manager Error 998

9. Help needed with Import and Export of text./image data

10. Need help importing data.

11. Newbie need help to import data

12. Need Help Importing data

13. Newbie needs help with Data Manager Importing