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

LoopDo 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|||Thanks a lot Blindman!!

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

thanks again,

TRoche|||"First timer having an insert problem."?

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