This is probably super basic but I've never built a procedure where I'm writing to a table where I need to pull the next id. Can someone walk me through how to find the next id, assign it on the insert, and then make sure that the next id table gets updated?
Thanks in advance!
Hi, take a look at this. im using next_id to auto create sources.
--select * from T_CAMPAIGN where description like 'DCM%' --campaign_no default_fund description --1688 NULL DCM Leads FY22 --select * from T_APPEAL where description like 'DCM%' --appeal_no campaign_no description --2593 1688 DCM Leads FY22 --select * from TX_APPEAL_MEDIA_TYPE where appeal_no = 2556 declare @createSources char(1) declare @appeal_no int declare @source_no int declare @source_name varchar(50) declare @segment_id int declare @source_Start_Dt datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) -- begining of today set @appeal_no = 2593 -- 2593 1688 DCM Leads FY22 set @createSources = 'N' create table #new_Sources(id int identity(1,1), source_name varchar(50), source_name_len int, segment_id int, param_1 varchar(99), param_2 varchar(99)) insert into #new_Sources(source_name, source_name_len, segment_id, param_1, param_2) select 'DCM_' + left(isnull(b.Param_Name,'') + case when isnull(b.Param_Name,'') > '' and isnull(c.Param_Name,'') > '' then '/' else '' end + isnull(c.Param_Name,''),46), len('DCM_' + isnull(b.Param_Name,'') + case when isnull(b.Param_Name,'') > '' and isnull(c.Param_Name,'') > '' then '/' else '' end + isnull(c.Param_Name,'')), a.ID, b.Param_Name, c.Param_Name from [LTR_DCM_LEAD_SEGMENTS] a left outer join [LTR_DCM_LEAD_PARAMETERS] b on a.param_1 = b.id left outer join [LTR_DCM_LEAD_PARAMETERS] c on a.param_2 = c.id where a.inactive = 'N' select * from #new_Sources order by segment_id select 'Duplicate new source name' select * from #new_Sources where source_name in(select source_name from #new_Sources group by source_name having count(id) > 1) order by source_name if exists(select * from #new_Sources where source_name_len > 50) begin print 'Source_Name length Issue' return end if @createSources = 'Y' BEGIN declare @done char(1) declare @placeholder int select @done = 'N', @placeholder = 0 While @done = 'N' Begin Select @placeholder = min(id)from #new_Sources where id > @placeholder If @placeholder is null Select @done = 'Y' Else BEGIN TRY BEGIN TRANSACTION select @source_name = left(source_name,50), @segment_id = segment_id from #new_Sources where id = @placeholder if not exists(select * from TX_APPEAL_MEDIA_TYPE where appeal_no = @appeal_no and source_name = @source_name) begin -- create new source exec @source_no = [dbo].ap_get_nextid_function @type = 'SL' insert into TX_APPEAL_MEDIA_TYPE (appeal_no, media_type,source_no,source_name,acq_dt,source_type,source_group,inactive,num_success_cust,start_dt) select @appeal_no,32,@source_no,@source_name,@source_Start_Dt,'B',82,'N',0,@source_Start_Dt -- update segment with source update LTR_DCM_LEAD_SEGMENTS set Source_no = @source_no where id = @segment_id end COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; print 'bad shit happened' END CATCH End END --delete from TX_APPEAL_MEDIA_TYPE where source_no in (23338,23342) -- source_name varchar(50) -- select * from T_NEXT_ID where type = 'SL' /* type description next_id parent_table parent_col module SL Source List 23339 TX_APPEAL_MEDIA_TYPE source_no F */ drop table #new_Sources
Thanks!