I need to update tables (sql server2k) on a frequent basis which are pretty huge(about 80Mil rows)
Right now, I use a fex which takes the data from a table(40mil rows)and does all the necessary calculations and holds as .ftm file then I use MODIFY FILE.. ..ON NOMATCH INCLUDE ON MATCH UPDATE to insert into that huge table. But, these tables keep growing every month so I need to come up with an optimum solution
Can anyone tell me if there is any effective/efficient solution for this kind of situation? I hope I'm not confusing anyone.

Thanks

As I know MODIFY performs very well, but on the hierarchical databases (especially FOCUS on MVS).
With the relational interfaces (including MS SQL) MODIFY INCLUDE syntax is translated to the series of INSERT statement which is not very effective for the large number of rows (I think it is not intended to use in such cases). It is better to use "native bulk load utility", in this case MS bcp utility, or MS SQL script/procedure with the BULK INSERT instruction. If memory serves me iWay ETL Manager uses bcp for MS SQL bulk load. You can invoke bcp with the ! syntax:


TABLE FILE LARGE_TABLE
PRINT *
ON TABLE SAVE AS LDFILE FORMAT COM
END
-RUN
! bcp with-the-appropriate-options

I do not remember the options and syntax of these utilities, but you can find information and examples with the Google, or on the Microsoft sites and "MS SQL Books Online".
An article about loading data into SQL Server

Another option is to SAVE the file to the location which is "known" for the MS SQL and use MS SQL DTS to load the data.

Hope this helps
Grzegorz

This message has been edited. Last edited by: <Mabel>,

I am still confused with the syntax with using bcp. This is what I found

bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]

but, my question is if I save my data as HOLD FILE FORMAT COM, how do I tell bcp (the datafile path). May be Im confused bcos I never used COM format to HOLD a file.
Please Help me in uderstanding the situation

Thanks

The WebFocus ETL solution uses Bulk Loading.
Since you have many of the WebFocus components
ETL would be an easy install.