Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unanswered: First Timer Having an Insert Problem

    Ok...I am stumped

    I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.

    What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.

    The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.

    I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.

    Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.

    Thank you in Advance!

    -TRoche


    do until GPSxRecord.EOF
    GPSx = GPSxRecord.Fields ("GPS_x").value
    tx = GPSxRecord.Fields ("tx").value

    GPSy = GPSyRecord.Fields ("GPS_y").value
    ty = GPSyRecord.Fields ("ty").value

    GPSz = GPSzRecord.Fields ("GPS_z").value
    tz = GPSzRecord.Fields ("tz").value

    'Executing the Insert Command
    DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

    DestCon.Execute DestCmd

    GPSxRecord.MoveNext
    GPSyRecord.MoveNext
    GPSzRecord.MoveNext

    Loop

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.

    It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    10

    Accepted Solution

    Thanks a lot Blindman!!

    This seems to have worked!! I would have never found that solution.

    thanks again,

    TRoche

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "First timer having an insert problem."?

    Maybe that's why you always remember your first.....

Posting Permissions

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