SQL newbie needs help on appending recs to database

[JazzcatCB]

I need to append some new records to a database that already has records. The existing records are all assigned unique ID numbers which are the primary key. The new records that I'm appending must start with ID number 3953. I'm using INSERT INTO with a new unique ID number for each new record.

The SQL statement I'm using is 'INSERT INTO Movies VALUES (ID, title, year, genres);'

The problem is that for some reason the INSERT INTO is not inserting the records in contiguous ascending order. After record 3952, I get record 4000, and the following records show up in ascending order for several records. At the end of the database, I find record 3953, and the previous records appear in DESCENDING order for several records.

Is there a better way to append records to my database? I've looked into using an autonumber field for the ID, but I'm concerned that it might renumber the existing records, which might invalidate the links that those records have with records in other databases (I got those records from an outside source, so I'm not certain that they are numbered contiguously.)

Any help would be much appreciated. Thanks.

[russell_g_1]

You could try using a select to get the next id number to use like this


insert into movies (id,title,year,genres)
select max(id)+1,'the title','the year','the genres' from movies

replacing the title, year and genres with some literals.