Going back in time to ADO we created a link to the text file (via Odbc Text
driver) and then issued a INSERT/SELECT FROM to copy the data from the link
into database proper ... for example;
CREATE "ADOX.Catal og" w_Catalog NO-ERROR.
w_Catalog:Activ eConnection = "<connectio n-string-to-mdb-database>".
CREATE "ADOX.Table " w_Table.
w_Table:Name = "zzData".
w_Table:ParentC atalog = w_Catalog.
w_Table:Propert ies("Jet OLEDB:Link Datasource") =
"<path-in-which-test-file-is-located>".
w_Table:Propert ies("Jet OLEDB:Remote Table Name") = "<name-of-test-file,
eg WSReport#txt>".
w_Table:Propert ies("Jet OLEDB:Create Link") = True.
w_Table:Propert ies("Jet OLEDB:Link Provider String")= "TEXT;HDR=N o".
w_Catalog:Table s:APPEND(w_Tabl e).
.... (and then )
CREATE "ADODB.Connecti on" w_DB NO-ERROR.
w_Sql = "INSERT INTO Booking (<fields>) SELECT <fieldsFROM zzData WHERE
<filter-if-required>".
w_DB:EXECUTE(w_ Sql, OUTPUT w_Cnt, 0).
w_Sql = 'DROP TABLE zzData'.
w_DB:EXECUTE(w_ Sql, OUTPUT w_Cnt, 0).
The code is from a non .Net 4gl, so "some" conversion is required, I have
placed <around most of the parameter elements - hopefully it will give you
some ideas. The down side is the use of the ADOX com for the schema link
(the "INSERT/SELECT FROM" statement you can do through managed code) - not
sure how much of an issue it will be though.
"rossum" <ro******@coldm ail.comwrote in message
news:v9******** *************** *********@4ax.c om...
On 3 Mar 2007 12:14:57 -0800, "mfrsousa" <mf******@gmail .comwrote:
rossum wrote:
For a bulk load of a database, such as you describe, it is often
better to switch off indexing on the affected tables before starting
the load and then rebuilding the indexes from scratch after the bulk
load has finished.
rossum
Right i was just reading this bulk thing (sorry my ignorance), the
problem is that i have tried something like this:
/*************** *************** ******** CODE
*************** *************** */
string conn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=|
DataDirectory|\ \db.mdb";
OleDbConnection oleconn = new OleDbConnection (conn);
oleconn.Open();
string sqlstr = "BULK INSERT INTO tblExtData (ext_disc, ext_num,
ext_track, ext_artist) FROM 'c:\\data1.lst' )";
OleDbCommand olecom = new OleDbCommand(sq lstr, oleconn);
olecom.ExecuteN onQuery();
/*************** *************** ******** CODE
*************** *************** */
and i get this error
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.
i think that the OleDB doesnt support the bulk command
could you point to some bulk reading or explain better please
thanks
I do not know MS SQL, all my experience was with Oracle SQL some time
ago.
Basically the sequence is:
Delete all indexes for tables to be loaded
Load data into tables
Rebuild/create indexes for tables just loaded
If you load data into an indexed table then each line inserted into
the table also requires an update to the index(es) for that table. By
dropping all the indexing before a bulk load you do not have the
overhead of re-indexing during the run.
Rebuilding the indexes from scratch is faster than trying to keep them
up to date while loading 350,000 lines into a table.
HTH
rossum
