Excel & Database 1

[tdong]

What is the best way to insert excel data to database with 1000 rows and 8 column each day. Need to keep history but not all the value in there changes everyday

1)One way is to insert all of them everyday too much data
2)Only insert the changes for each row and columns <--need some idea on this
I am using VB6 or VBA and sql 2000

Thanks

 

[SkipVought]

Hi,

How do you know which rows need to UPDATE the table? Is there a date stamp?

Sure, use ADO and INSERT into the table. Is there a problem?

Skip,

_[red][/red]

 

[tdong]

The thing is I don't know which rows need to update. But i can check for it since the excel will be updated daily by bloomberg. Everyday some rows mayhave new value some doesn't change at all

The problem is if I insert 1000 row and 8 column everyday it would be over kill. For number 2 solution. I can get the excel data into ADO recordset then for each row in the recordset I will compare to the value already in the database or insert it if it doesn't exist. This mean I am using Name->Value pair. What do you think ?

my table like this. assume 1 row two column in excel
ID ColumnNameofExcel Value Record_Ts
1 A 1 10/10/2006
1 B 2 10/10/2006
1 A 4 10/11/2006 <---since on 10/11/2006 only column A change columm B does change I don't insert anything

 

[SkipVought]

Sure. First do a Select inquiry.

If there is no recordset returned, then INSERT.

Skip,

_[red][/red]