SQL guaranteed start/end index?

Question

I (will) have hundreds of thousand of records where i insert once, never update with many rows holding the same previousId. Can i guaranteed a start/end index? where i insert X number of rows in table_c with a transaction and write the start and end (or start and length or end and length) into table_b instead of having each row hold table_b ID?

if so how do i write the SQL? i was thinking

begin transaction
insert XYZ rows into tbl_c
c_rowId = last_insert_rowid
insert table_b with data + start=c_rowId-lengthOfInsert, end=c_rowId;
commit; end transaction

would this work as i expect?

Answer 1

You can lock the full table in sql server by using tablockx.

so:

begin the transaction, 
select max(txnid) from table with (tablockx)  -- now the table is locked

--figure out how many more you are going to insert 
lastId = maxNum + numToInsert

set allow insert auto_increment on insert -- not sure what this is

while (moreToInsert)
  insert int table (id,x,y) values (id+1,'xx','yy')

commit transaction

The problem with this though is that it totally locks the table. An auto incrementing column (auto_increment (mysql) or identity mssql) might be what you really want and would not limit access to the whole table. (This was noted in another answer)

Answer 2

It seems like what you want is an autonumber column. In most DBMS, you can define this column as part of the table definition, and it will number the rows automatically. You can use a function to get the ID of the last row inserted; in SQL Server (T-SQL), you can use the SCOPE_IDENTITY() function. If the IDs -have- to be a certain value or range of values, you may need to do it manually and use a locking hint to prevent another concurrent transaction from modifying your identifier information.