Plzz Help me in replacing the Cursor

Question

Post reply

Plzz Help me in replacing the Cursor

inampudianil

Ten Centuries

Points: 1274
More actions
August 11, 2010 at 10:45 pm

#157145

Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. plzz it's very urgent......
CREATE proc [dbo].[sp_Build_Eam_Member_Eligibility_Step2] AS
declare @Eligibility_Id bigint,
@Subscriber_Id int,
@Prev_Subscriber_Id int,
@Prev_Plan_Record int;
declare @Span_Num int
declare c1 cursor for
Select Eligibility_Id, Subscriber_Id,
(Select Eligibility_Id
From dbo.Eam_Member_Eligibility
Where Subscriber_Id = ME.Subscriber_Id and
End_Date = dateadd(d,-1, me.Start_Date)) Prev_Plan_Record
From dbo.Eam_Member_Eligibility ME
Order By Subscriber_Id, Start_Date
--select Start_Date,dateadd(d,-1, Start_Date) from Eam_Member_Eligibility
open c1;
fetch next from c1 into @Eligibility_Id, @Subscriber_Id, @Prev_Plan_Record
set @Prev_Subscriber_Id = 0
set @Span_Num = 0
while @@Fetch_Status = 0
Begin
If @prev_Subscriber_Id = @Subscriber_Id
Begin
if @Prev_Plan_record is null
set @Span_Num = @Span_Num + 1
End
else
Begin
set @Prev_Subscriber_Id = @Subscriber_Id
set @Span_Num = 1
End
update dbo.Eam_Member_Eligibility
set Span_Num = @Span_Num
where Eligibility_Id = @Eligibility_Id
fetch next from c1 into @Eligibility_Id, @Subscriber_Id, @Prev_Plan_Record
End
close c1
deallocate c1
CREATE TABLE [dbo].[Eam_Member_Eligibility](
[Eligibility_Id] [int] IDENTITY(1,1) NOT NULL,
[Span_Num] [tinyint] NULL,
[Eam_Span_Id] [int] NULL,
[Member_Code_Num] [bigint] NULL,
[Subscriber_Id] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Medicare_Id] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cms_Contract_Id] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[Last_Ind] [tinyint] NULL CONSTRAINT [DF_Eam_Member_Eligibility_Last_Ind] DEFAULT ((0)),
[Source_Table] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created_Datetime] [datetime] NULL,
[Last_Updated_Datetime] [datetime] NULL,
CONSTRAINT [PK_Eam_Member_Eligibility] PRIMARY KEY CLUSTERED
(
[Eligibility_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [Data]
) ON [Data]
ALTER TABLE [dbo].[Eam_Member_Eligibility] ADD CONSTRAINT [PK_Eam_Member_Eligibility] PRIMARY KEY CLUSTERED
(
[Eligibility_Id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Data]
CREATE NONCLUSTERED INDEX [IX_Eam_Member_Eligibility_2] ON [dbo].[Eam_Member_Eligibility]
(
[Member_Code_Num] ASC,
[Start_Date] ASC,
[End_Date] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Indexes]
CREATE NONCLUSTERED INDEX [IX_Eam_Member_Eligibility_1] ON [dbo].[Eam_Member_Eligibility]
(
[Subscriber_Id] ASC,
[Start_Date] ASC,
[End_Date] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Indexes]
Here is data for testing
===============
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11858833,11437,'071720358','429628828A','R3444',cast('2007-07-01 00:00:00.000' as datetime),cast('2008-01-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11937852,138411,'100138411','247989875A','R9896',cast('2009-12-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12116021,359,'083440099','173386283A','H5665',cast('2009-01-01 00:00:00.000' as datetime),cast('2009-10-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11974592,111986,'070960002','256826362A','R9896',cast('2007-04-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12032215,112798,'073330077','257388350A','R9896',cast('2007-12-01 00:00:00.000' as datetime),cast('2008-01-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12002940,59755,'080310070','464395945A','R6801',cast('2008-02-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11952340,117121,'072770785','258849062A','R9896',cast('2007-10-01 00:00:00.000' as datetime),cast('2008-02-29 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12382504,24087,'071910049','489302928A','R3444',cast('2007-08-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12056251,24087,'071910049','489302928A','R3444',cast('2008-04-01 00:00:00.000' as datetime),cast('2008-04-30 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12149447,160460,'100160460','429499773A','R3444',cast('2010-03-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(3,12218449,24087,'071910049','489302928A','R3444',cast('2010-04-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12115024,17318,'071720340','431684228A','R3444',cast('2007-07-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,11946647,17318,'071720340','431684228A','R3444',cast('2010-05-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12202852,14802,'071980013','430746631A','R3444',cast('2007-08-01 00:00:00.000' as datetime),cast('2008-03-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11903795,74146,'082610062','241466147A','R9896',cast('2008-10-01 00:00:00.000' as datetime),cast('2008-11-30 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12324990,22746,'071630187','481342737A','R3444',cast('2007-07-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12062815,97811,'070250017','251681586A','R9896',cast('2007-02-01 00:00:00.000' as datetime),cast('2009-01-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12017774,60128,'072390238','464527205B','R6801',cast('2007-09-01 00:00:00.000' as datetime),cast('2007-09-30 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12290348,29334,'072680275','513360829B','R3444',cast('2007-10-01 00:00:00.000' as datetime),cast('2008-11-30 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12343988,115874,'072490902','258508499A','R9896',cast('2007-09-01 00:00:00.000' as datetime),cast('2008-12-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12027690,115874,'072490902','258508499A','R9896',cast('2010-01-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12041222,108967,'072600120','255661943A','R9896',cast('2008-10-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11795804,53747,'072431004','460489051A','R6801',cast('2007-09-01 00:00:00.000' as datetime),cast('2008-03-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11782514,111316,'072700276','256622568A','R9896',cast('2007-10-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12282428,127082,'070610038','423326422A','R9896',cast('2007-03-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12152796,127082,'070610038','423326422A','R9896',cast('2009-05-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12317032,35081,'071550051','433522652A','R6801',cast('2009-04-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12005282,137381,'100137381','256586928A','R9896',cast('2009-12-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12428721,81750,'071230964','248542981A','R9896',cast('2010-08-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11858305,108265,'063500069','255549908A','R9896',cast('2007-01-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11944175,64955,'072630667','467586709A','R6801',cast('2007-10-01 00:00:00.000' as datetime),cast('2008-08-31 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,11884484,64955,'072630667','467586709A','R6801',cast('2008-10-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12092382,61631,'063410040','465504296A','R6801',cast('2007-01-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11773155,153720,'100153720','252806774A','R9896',cast('2010-01-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12020868,169902,'100169902','570045155A','R3444',cast('2010-05-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12289852,24511,'071350316','490321382A','R3444',cast('2007-06-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11847165,69717,'070650099','051324649A','R9896',cast('2007-03-01 00:00:00.000' as datetime),cast('2007-12-31 00:00:00.000,0' as datetime),'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,11982156,69717,'070650099','051324649A','R9896',cast('2008-09-01 00:00:00.000' as datetime),cast('2008-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12051436,87799,'081570107','249665791A','R9896',cast('2010-01-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12268317,89447,'071420042','250049709A','R9896',cast('2007-06-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12430544,179041,'100179041','250443795A','R9896',cast('2010-08-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,12388420,104910,'073270049','254404012A','R9896',cast('2007-12-01 00:00:00.000' as datetime),cast('2008-02-29 00:00:00.000' as datetime),0,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(2,12388421,104910,'073270049','254404012A','R9896',cast('2010-01-01 00:00:00.000' as datetime),cast('2010-06-30 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11877971,73057,'063530118','225902729A','R9896',cast('2007-01-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
insert into [dbo].Eam_Member_Eligibility_asdf([Span_Num] ,[Eam_Span_Id] ,[Member_Code_Num] ,[Subscriber_Id] ,[Medicare_Id] ,[Cms_Contract_Id],[Start_Date] ,[End_Date],[Last_Ind],[Source_Table],[Created_Datetime],[Last_Updated_Datetime]) values(1,11948069,62453,'082120086','466064640A','R6801',cast('2008-08-01 00:00:00.000' as datetime),cast('9999-12-31 00:00:00.000' as datetime),1,'tbENRLSpans',cast('2010-06-18 17:20:00.000' as datetime),cast('2010-06-18 17:20:00.000' as datetime))
Please assist me on this....
Thanks in Advance
Anil

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Duplicate post regarding the same subject.

Discussion already started here.

@anil-2: Why don't you try use the sample code already provided by Wayne and see if you can get it to work as you need it?

Reposting the whole question without any refernce to the previous post is just a waste of resources.

Side note: One reason you didn't get any more replies on that previous post might be your frequently use of "dude".

This is a professional forum, not a chat room.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

rvasanth SSC Eights! Points: 873 More actions · Answer 2

Insert of using cursor, you can use the temp table and while loop

Insert all the records into a temp table and start a while loop update for no of temp table records count and inside while loop you can do your stuff