Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Haaksbergen, Netherlands

    Question Unanswered: How to put data from a recordset into a table

    I want to transfer a recordset (derived from an Oracle datasource) into
    an SQL2000 Server table using VBScript in a ActiveX Script Task using a DTS.

    Currently I use the OPENROWSET (and OPENQUERY) method, however
    the lenght of the querytext seems to be limited to 8192 bytes.
    At this moment I have reached the limit of this lenght, and I am looking
    for a solution.

    Futher info: The query returns large recordsets of 100,000s of records with 100s of columns. Because of it's complex structure, the standard data transformation in SLQ2000 is not an option.

    Using a substitute like:

    varRecords = rst1.GetRows
    For intI = 0 To UBound(varRecords, 2)
    For intJ = 0 To UBound(varRecords, 1)
    rst2(intJ) = varRecords(intJ, intI)

    works fine but is much to slow.

    Can anyone help me to find a solution?

  2. #2
    Join Date
    Sep 2003
    Would it be possible to vertically split the data you're importing into multple queries? Such as:

    insert table1
    select * from openquery(oracleserver, 'select fld1, fld2.....fld50 from oracle.table blah, blah, blah')

    insert table2
    select * from openquery(oracleserver, 'select fld1, fld51.....fld100 from oracle.table blah, blah, blah')

    insert table3
    select * from openquery(oraclesrver, 'select fld1, fld101....fld150 from oracle.table blah, blah, blah')

    Then join them together to insert into thier permanant home:

    insert FinalTable
    select table1.fld1, fld2, fld3.....fld150
    from table1, table2, table3
    where table1.fld1 = table2.fld1
    and table2.fld1 = table3.fld1

    just a thought

  3. #3
    Join Date
    Jun 2004
    Haaksbergen, Netherlands
    Good thought, however difficult to implement because
    the selected fields can be variable.

    I am looking for a more simple solution.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts