dayly table update

4 Nov 2005 2:37 PM

Xavier

hello,

i must dayly update a table in my database with the values of a CSV file
(~300000 entries)

example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
......
245433 router 126,33

Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.

How can this be done?
thanks

4 Nov 2005 3:11 PM

marcmc

One recommendation could be

1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted

a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
                         WHERE SURR.Col1= BCP.Col1 )

b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT     Col1, Col2
    FROM     ot_su_daily_csv SURR(nolock)
    ORDER BY Col1

6 Nov 2005 3:14 PM

Xavier

thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc   699,44

the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz   699,44 (product description is modified)
245433 router 126,33   -> ok will be detected and updated

......
how to make a correct update in this situation ....

thanks
Xavier

6 Nov 2005 11:07 PM

Hugo Kornelis

On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

Show quote

>thanks for the recommendation - it works well if only each day new values in
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc   699,44
>
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz   699,44 (product description is modified)
>245433 router 126,33   -> ok will be detected and updated
>
>.....
>how to make a correct update in this situation ....
>
>thanks
>Xavier

Hi Xavier,

Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:

UPDATE      t
SET         Descr = s.Descr,
            Price = s.Price,
            ... (other columns)
FROM        TheTable     AS t
INNER JOIN StagingTable AS s
      ON    s.KeyColumn = theTable.keyColumn
WHERE       t.Descr <> s.Descr
OR          t.Price <> s.Price
OR          ... (other columns)

INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT      KeyColumn, Descr, Price, ... (other columns)
FROM        Stagins AS s
WHERE NOT EXISTS
(SELECT    *
FROM      TheTable AS t
WHERE     t.KeyColumn = s.KeyColumn)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

7 Nov 2005 11:42 AM

Xavier

thanks,
Xavier

Show quote

"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> >thanks for the recommendation - it works well if only each day new values in
> >the csv-file are attached.
> >But in my csv file some colums of the articles are changed - like in the
> >example
> >example: - day1
> >000001 monitor 234,66
> >000003 pc   699,44
> >
> >the next day - day 2
> >000001 monitor 230,03 (price is modified...)
> >000003 pc-3,4GHz   699,44 (product description is modified)
> >245433 router 126,33   -> ok will be detected and updated
> >
> >.....
> >how to make a correct update in this situation ....
> >
> >thanks
> >Xavier
>
> Hi Xavier,
>
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
>
> UPDATE      t
> SET         Descr = s.Descr,
>             Price = s.Price,
>             ... (other columns)
> FROM        TheTable     AS t
> INNER JOIN StagingTable AS s
>       ON    s.KeyColumn = theTable.keyColumn
> WHERE       t.Descr <> s.Descr
> OR          t.Price <> s.Price
> OR          ... (other columns)
>
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT      KeyColumn, Descr, Price, ... (other columns)
> FROM        Stagins AS s
> WHERE NOT EXISTS
> (SELECT    *
>   FROM      TheTable AS t
>   WHERE     t.KeyColumn = s.KeyColumn)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>