February 18, 2011 at 8:30 pm
I have a set of records that I need to insert to an existing table. The existing table has a field named record which is an int data type, not null, but not an identity field. I have 1400 records to insert into the table. How can insert the new records into the table and have the record field start it's increment from the previous record.
Thanks
February 18, 2011 at 10:01 pm
recordid even if is not an identity, is it a primary key, if so then get the max(recordid) and programitically insert records incrementing recordid with the number of records. as the number of records to insert is not big, it wont be a time and resource consuming job.
alternatively you can create a temp table with identity starting from max(recordid)+1 and insert your records in it. then copy the records from temp table to the table you want to insert records.
February 19, 2011 at 7:40 am
Even if the record id is not primary key, as long as it's unique on the exist data and in a increment trend when they were entered (or it will be the trend from now on), you can use the max(record id) to get the last id used. If you already have your 1400 new records in another table, combined with row_number() function, you can insert all the new records in one select statement similar to this:
declare @maxid int
select @maxid = max(recordid) from...
insert into ...
select recordid = @maxid + row_number() over (order by <order columns you want records to be inserted>), ...
from ...
February 19, 2011 at 8:42 am
Thanks for the help!!! I appreciate it!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply