I am doing a big batch of "INSERT" scripts to transfer data from an old DB into a new one.

I am seeing a situation where the data transfer is missing a few of the records in the old
table.

I am pulling my hair out trying to figure why.

Roughly what I am doing:

DELETE FROM NewTable

;

ALTER TABLE NewTable LAST AUTOINC 0

;


ALTER NewTable
//remove indexes that might cause problems during insert
//add dummy columns to accept data which can be deleted later.

;

INSERT INTO NewTable
(
 NewFields ...
)  

SELECT

 OldFields ...

FROM OldTable
LEFT JOIN OtherOldTable
;


------------------------


When I run the SELECT part at the end by itself I return 15,000 records. When I run the
INSERT I only get 14,700. Of course I can run the SELECT with an "INTO" so that I get all
the 15,000 records. However, shouldn't the INSERT work anyway!!!

Adam

adam


DBISAMQuery's has an event which should help you track down the cause - OnDataLost. Try creating one and see what it tells you.

Roy Lambert

Adam,

<< When I run the SELECT part at the end by itself I return 15,000 records.
When I run the INSERT I only get 14,700. Of course I can run the SELECT with
an "INTO" so that I get all the 15,000 records. However, shouldn't the
INSERT work anyway!!! >>

It depends - are you inserting any records that would cause a validity check
(min/max) to fail or a primary/unique check to fail ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>>It depends - are you inserting any records that would cause a validity check
>>(min/max) to fail or a primary/unique check to fail ?

... that is exactly what is messing with my head! I am droping the indexes before I run
the query, so there should be literally nothing stopping the inserts & yet I lose 305
*random* records from the result set ... I literally cannot find any feature of the droped
records that can link even a few of them together!

Adam

adam


If the tables aren't to big, or confidential, would you like me to have a look?

Roy Lambert

Adam,

<< .. that is exactly what is messing with my head! I am droping the indexes
before I run the query, so there should be literally nothing stopping the
inserts & yet I lose 305 *random* records from the result set ... I
literally cannot find any feature of the droped records that can link even a
few of them together! >>

If you want to send me the SQL and tables, I'll be happy to tell you what is
going on.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you for the offer guys ... I got it to work using a SELECT INTO & then using RESTRUCTURE on the resulting saved table ... I just had to get
the job done by last week. I would still like to understand what was happening. I will look at it again if I get a minute & write in with the outcome