June 28, 2004 at 6:23 am
HI all
I need to insert rows to a table the table has a unique key, integer for item_id containing a seqential number, (not and identity columne)the talble is [provided be a third party vendor so can modify it's design.
So I need to start with a base value, (last value of item_id + 1)
and increment by one for each row I insert. How would you suggest doing this?
Thanks
June 28, 2004 at 7:25 am
Use a temp table and identity column
create table #temp1 (rowid int, col1 char(1))
insert into #temp1 values (1,'A')
insert into #temp1 values (2,'B')
insert into #temp1 values (3,'C')
create table #temp (rowid int identity(1,1), col1 char(1))
insert into #temp (col1) values ('D')
insert into #temp (col1) values ('E')
insert into #temp (col1) values ('F')
insert into #temp1
(rowid, col1)
select (select max(rowid) from #temp)+rowid, col1
from #temp
select * from #temp1
drop table #temp1
drop table #temp
Far away is close at hand in the images of elsewhere.
Anon.
June 29, 2004 at 12:56 am
You can use an 'INSTEAD OF' trigger. For optimal performance, you could also store the current maximum in an other table. Example below uses select max() to get the current maximum.
create table dbo.tbl( item_id int constraint UK_tbl UNIQUE, f2 varchar(10) )
go
create trigger tbl_i
on dbo.tbl
INSTEAD OF insert
as
begin
declare @maxitem_id int
declare @f2 varchar(10)
declare ins cursor LOCAL FAST_FORWARD for select f2 from inserted
-- Get the current maximum. Use 1 if no rows ...
set @maxitem_id = null
select @maxitem_id = max(item_id) + 1 from dbo.tbl
if @maxitem_id is null set @maxitem_id = 1
open ins
goto NEXTins
while @@fetch_status = 0
begin
insert dbo.tbl (item_id,f2 ) values (@maxitem_id,@f2)
set @maxitem_id = @maxitem_id + 1
NEXTins: fetch ins into @f2
end
close ins
deallocate ins
end
go
insert tbl values ( 10,'test 1' )
insert tbl values ( 10,'test 2')
insert tbl values ( 10,'test 3' )
insert tbl values ( 10,'test 4' )
insert tbl values ( 10,'test 5' )
go
select * from tbl
June 30, 2004 at 11:19 am
Bert
thanks , I never would have thought of an Instead of Trggger
June 30, 2004 at 11:20 am
David,
thanks for your reply, looks like a great technique.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply