February 2, 2009 at 3:05 am
hi all,
i am facing certain problem in writing a cursor.
my requirement is that when cursor fetches each row i want to access all the columns of the row. problem is that there are arount 166 columns so if i use variable then i need 166 variables.that is really a very tedious job.so if by any means i fetch whole row from cursor and can store that whole row in some rowtype or something so that can use any column when needed then my problem will be solved..
thanks for your help and time..
need it urgently.
February 2, 2009 at 3:50 am
Can you post all or part of the code which you are currently attempting to use, with an explanation of what you are trying to achieve?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 4:50 am
FIRST OF ALL THANX A LOT
my code is like
Create PROCEDURE spInsert
AS
BEGIN
declare @clientCode varchar(10)
declare @dbstring varchar(50)
set @clientCode='kh'
set @dbstring = @clientCode + 'idsnetsql'
print @dbstring
exec('use ' + @dbstring +
'
Select * into TempInscertificate from Inscertificate where 1=2
select * into TempInsCoCoverage from InsCoCoverage where 1=2
declare @producerid varchar(10)
declare @issuedate datetime
declare @Producer varchar(20)
declare @insured varchar(20)
declare @Issuedate1 varchar(20)
declare @CARRIER_NAME1 varchar(50)
declare @CARRIER_NAME2 varchar(50)
declare @CARRIER_NAME3 varchar(50)
declare @CARRIER_NAME4 varchar(50)
declare @CARRIER_NAME5 varchar(50)
declare @XX_NAIC_NUMBER1 varchar(50)
declare @XX_NAIC_NUMBER2 varchar(50)
declare @XX_NAIC_NUMBER3 varchar(50)
declare @XX_NAIC_NUMBER4 varchar(50)
declare @XX_NAIC_NUMBER5 varchar(50)
Declare FeedRow Cursor
For
Select
Producer_Name,
Insured_name1,
Issue_Date,
IsNull(CARRIER_NAME1,'''') as CARRIER_NAME1,
IsNull(CARRIER_NAME2,'''') as CARRIER_NAME2,
IsNull(CARRIER_NAME3,'''') as CARRIER_NAME3,
IsNull(CARRIER_NAME4,'''') as CARRIER_NAME4,
IsNull(CARRIER_NAME5,'''') as CARRIER_NAME5,
IsNull(XX_NAIC_NUMBER1,'''') as XX_NAIC_NUMBER1,
IsNull(XX_NAIC_NUMBER2,'''') as XX_NAIC_NUMBER2,
IsNull(XX_NAIC_NUMBER3,'''')as XX_NAIC_NUMBER3,
IsNull(XX_NAIC_NUMBER4,'''') as XX_NAIC_NUMBER4,
IsNull(XX_NAIC_NUMBER5,'''') as XX_NAIC_NUMBER5
from idscomsql.dbo.feedrecord
Open FeedRow
Fetch Next from FeedRow Into
@Producer,
@insured,
@Issuedate1,
@CARRIER_NAME1,
@CARRIER_NAME2,
@CARRIER_NAME3,
@CARRIER_NAME4,
@CARRIER_NAME5,
@XX_NAIC_NUMBER1,
@XX_NAIC_NUMBER2,
@XX_NAIC_NUMBER3,
@XX_NAIC_NUMBER4,
@XX_NAIC_NUMBER5
While @@Fetch_Status = 0
Begin
Print @Producer
Print @insured
Print @Issuedate1
Print @CARRIER_NAME1
Print @CARRIER_NAME2
Print @CARRIER_NAME3
Print @CARRIER_NAME4
Print @CARRIER_NAME5
Print @XX_NAIC_NUMBER1
Print @XX_NAIC_NUMBER2
Print @XX_NAIC_NUMBER3
Print @XX_NAIC_NUMBER4
Print @XX_NAIC_NUMBER5
select top 1 @producerid=producerid from idscomsql.dbo.Producermaster where producer like ''%'' + @Producer + ''%''
print @producerid
Declare @vendorNum varchar(15)
declare @VendorNum_ins varchar(200)
declare @ProducerId_ins varchar(200)
declare @issuedate_ins datetime
select top 1 @vendorNum=VENDORNUM from INSPOLICYMASTER WHERE INSURED LIKE ''%'' + @insured +''%''
Select @VendorNum_ins=VendorNum,@ProducerId_ins=ProducerId,@issuedate_ins=Issuedate from Inscertificate where VendorNum=@vendorNum And ProducerId=@producerid And Issuedate=convert(datetime,@Issuedate1)
IF ( @producerid=@ProducerId_ins And @vendorNum=@VendorNum_ins And convert(varchar,@Issuedate,111)=convert(varchar,@issuedate_ins,111))
BEGIN
PRINT @producerid
end
ELSE
BEGIN
--insert into TempInscertificate (vendornum,producerid,issuedate,PECov) values (@VendorNum,@ProducerId,convert(datetime,@Issuedate1),0)
INSERT INTO TempInscertificate
(VendorNum,ProducerId,IssueDate,GLCov,EQCov,ELCov,ALCov,WCCov,GRCov,PRCov,PLCov,CGCov,POCov,BMCov,PGCov,BICov,EICov,LLCov,BRCov,FLCov,RRCov,FOCov,WLCov,BACov,CRCov,FBCov,JBCov,PDCov,BroadAdditionalIns,LocationSpecific,JobSpecific,PrimaryInsurance,CertHolder,CancellationDays,CancelMod,DocSigned,DocSignedComp,FirstUpdateDate,LastUpdateDate,OCCov,AsCov,GKCov,CertWord,EDCov,ACCov,PICov,AVCov,HGCov,ImmunityClause,BGCov,DOCov,EPCov,MMCov,PBCov,PACov,SACov,HLCov,CCCov,ADCov,SLCov,OMCov,EVCov,CPCov,SSRec,LPCov,APCov,HOCov,RGCov,VPCov,EFCov,TICov,SMCov,CSCov,CECov,IECov,IFCov,PECov)
VALUES
(@VendorNum,@ProducerId,convert(datetime,@Issuedate1),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,getdate(),getdate(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
IF @CARRIER_NAME1 <> ''''
Begin
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
VALUES
(@VendorNum,''A'',@ProducerId,convert(datetime,@Issuedate1),@CARRIER_NAME1,Getdate(),@XX_NAIC_NUMBER1)
End
IF @CARRIER_NAME2 <> ''''
Begin
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
VALUES
(@VendorNum,''B'',@ProducerId,convert(datetime,@Issuedate1),@CARRIER_NAME2,Getdate(),@XX_NAIC_NUMBER2)
End
IF @CARRIER_NAME3 <> ''''
Begin
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
VALUES
(@VendorNum,''C'',@ProducerId,convert(datetime,@Issuedate1),@CARRIER_NAME3,Getdate(),@XX_NAIC_NUMBER3)
End
IF @CARRIER_NAME4 <> ''''
Begin
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
VALUES
(@VendorNum,''D'',@ProducerId,convert(datetime,@Issuedate1),@CARRIER_NAME4,Getdate(),@XX_NAIC_NUMBER4)
End
IF @CARRIER_NAME5 <>''''
Begin
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
VALUES
(@VendorNum,''E'',@ProducerId,convert(datetime,@Issuedate1),@CARRIER_NAME5,Getdate(),@XX_NAIC_NUMBER5)
End
End
Fetch Next from FeedRow Into
@Producer,
@insured,
@Issuedate1,
@CARRIER_NAME1,
@CARRIER_NAME2,
@CARRIER_NAME3,
@CARRIER_NAME4,
@CARRIER_NAME5,
@XX_NAIC_NUMBER1,
@XX_NAIC_NUMBER2,
@XX_NAIC_NUMBER3,
@XX_NAIC_NUMBER4,
@XX_NAIC_NUMBER5
End
close FeedRow
Deallocate FeedRow
'
)
End
in feedrecord table there is around 166 columns. i used many variables like @Producer,
@insured,
@Issuedate1,
@CARRIER_NAME1,
@CARRIER_NAME2,
@CARRIER_NAME3,
@CARRIER_NAME4,
@CARRIER_NAME5,
@XX_NAIC_NUMBER1,
@XX_NAIC_NUMBER2,
@XX_NAIC_NUMBER3,
@XX_NAIC_NUMBER4,
@XX_NAIC_NUMBER5
to fetch each record. my current requirement is that i have to access all the records from the feedrecord table in order to insert in other tables.
so please tell me how can i avoid using so many variables.??
February 2, 2009 at 6:27 am
From a quick glance, this doesn't need a cursor at all.
One question - if all 5 of the carrier names are blank, do you want 5 records in the table or just 1?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 6:50 am
You're welcome.
This isn't a complicated process and there's certainly no need for a cursor. Processing the feed table row by row,
with up to six individual inserts per row, is really going to hit performance.
The first part of the cursor loop is validation, a couple of values are looked up against other tables then these new
values are compared against the table Inscertificate - this looks like a dupecheck, to see if the row in the feed table already exists.
Now, if you could do all of this first part in a set-based manner, then you've done most of the work needed to make this
a fast (and simple) set-based procedure.
Here's a replacement for the validation step:
SELECT f.*,
c.VENDORNUM,
pm.producerid
INTO #feedrecord
FROM idscomsql.dbo.feedrecord f
LEFT JOIN (SELECT MIN(producerid) AS producerid, producer FROM idscomsql.dbo.Producermaster GROUP BY producer) pm ON pm.producer LIKE '%' + f.Producer_Name + '%'
LEFT JOIN (SELECT MIN(VENDORNUM) AS VENDORNUM, INSURED FROM INSPOLICYMASTER GROUP BY INSURED) im ON im.INSURED like '%' + f.Insured_name1 + '%'
LEFT JOIN Inscertificate c ON c.VendorNum = im.VendorNum AND c.ProducerId = pm.producerid AND c.Issuedate = convert(datetime,f.Issue_Date)
WHERE c.VendorNum IS NULL AND c.ProducerId IS NULL
I'm putting the output into a #temp table because there will later be up to five pulls from the same data pool - why not limit those to the rows which we're interested in.
The two lookup tables are preaggregated and left joined to the feed table. You may find with testing that a full join is more appropriate, without test data it's not possible to tell.
The destination table Inscertificate is also left joined so that you will get output from the query even when there isn't a matching row,
then matching rows are suppressed from the output with the WHERE clause.
You should check this carefully against your data.
Doing the inserts from the #temp table is straightforward:
INSERT INTO TempInscertificate
(VendorNum,ProducerId,IssueDate,GLCov,EQCov,ELCov,ALCov,WCCov,GRCov,PRCov,PLCov,CGCov,POCov,BMCov,PGCov,BICov,EICov,LLCov,
BRCov,FLCov,RRCov,FOCov,WLCov,BACov,CRCov,FBCov,JBCov,PDCov,BroadAdditionalIns,LocationSpecific,JobSpecific,PrimaryInsurance,CertHolder,
CancellationDays,CancelMod,DocSigned,DocSignedComp,FirstUpdateDate,LastUpdateDate,OCCov,AsCov,GKCov,CertWord,EDCov,ACCov,PICov,AVCov,
HGCov,ImmunityClause,BGCov,DOCov,EPCov,MMCov,PBCov,PACov,SACov,HLCov,CCCov,ADCov,SLCov,OMCov,EVCov,CPCov,SSRec,LPCov,APCov,
HOCov,RGCov,VPCov,EFCov,TICov,SMCov,CSCov,CECov,IECov,IFCov,PECov)
SELECT
VENDORNUM, producerid, convert(datetime,Insured_name1),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
getdate(),getdate(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM #feedrecord
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
SELECT
VENDORNUM, 'A', producerid, convert(datetime,Insured_name1), CARRIER_NAME1, Getdate(), XX_NAIC_NUMBER1
FROM #feedrecord
WHERE CARRIER_NAME1 IS NOT NULL AND CARRIER_NAME1 <> ''
INSERT INTO TempInsCoCoverage
(VendorNum,LetterId,ProducerId,IssueDate,InsuranceCompany,FirstUpdateDate,NAIC)
SELECT
VENDORNUM, 'B', producerid, convert(datetime,Insured_name1), CARRIER_NAME2, Getdate(), XX_NAIC_NUMBER2
FROM #feedrecord
WHERE CARRIER_NAME2 IS NOT NULL AND CARRIER_NAME2 <> ''
So, no variables and no cursor. You will still have to work with lots of column names however.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 6:51 am
GilaMonster (2/2/2009)
From a quick glance, this doesn't need a cursor at all.One question - if all 5 of the carrier names are blank, do you want 5 records in the table or just 1?
Gail - nice weapon! Horrible haircut:P
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 7:09 am
Chris Morris (2/2/2009)
Gail - nice weapon! Horrible haircut:P
I'll change the avatar back in a few days. That's the second comment about the haircut.
Funny thing is, it's not far different from what I have in real life. 😀 (photo here)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 7:55 am
GilaMonster (2/2/2009)
Chris Morris (2/2/2009)
Gail - nice weapon! Horrible haircut:PI'll change the avatar back in a few days. That's the second comment about the haircut.
Funny thing is, it's not far different from what I have in real life. 😀 (photo here)
That is funny! The profile angles are almost the same.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 9:20 pm
Thanx a lot chris. thanx for your help. whith this i guess i'll be able to achive my goal.:)
February 3, 2009 at 12:22 am
one more thing chris.....
SELECT f.*,
c.VENDORNUM,
pm.producerid
INTO #feedrecord
FROM idscomsql.dbo.feedrecord f
LEFT JOIN (SELECT MIN(producerid) AS producerid, producer FROM idscomsql.dbo.Producermaster GROUP BY producer) pm ON pm.producer LIKE '%' + f.Producer_Name + '%'
LEFT JOIN (SELECT MIN(VENDORNUM) AS VENDORNUM, INSURED FROM INSPOLICYMASTER GROUP BY INSURED) im ON im.INSURED like '%' + f.Insured_name1 + '%'
LEFT JOIN Inscertificate c ON c.VendorNum = im.VendorNum AND c.ProducerId = pm.producerid AND c.Issuedate = convert(datetime,f.Issue_Date)
WHERE c.VendorNum IS NULL AND c.ProducerId IS NULL
the code above will fetch all the records into feedtable or i need to apply something else for it.
February 3, 2009 at 2:36 am
vikas.saxena (2/3/2009)
one more thing chris.......snip..
the code above will fetch all the records into feedtable or i need to apply something else for it.
The purpose of this statement is to pull rows into a temp table - only those rows which are not already in your target db i.e. the ones which are the source for your inserts.
Check it rigorously.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2009 at 2:46 am
Thanx chris:)
February 3, 2009 at 4:14 am
hi chris...
need your help once again...
actully producerid is of varchar type so i guess we cant use min for distinct producerid.and for same producer there are numerous producerid. so the records which i am getting in #feedrecord contains same record for different producer.
i want that whatever the number of producers are there it should record only one i.e. top 1 producerid. i hope you understood wat i said.
for your help please find the excel sheet of the result of validation select query.
Thanks a lot
February 3, 2009 at 4:31 am
You can use min on varchar. Hopwever, it might not give the same result as you were originally getting. This is closer, using correlated subqueries:
SELECT d.*
INTO #feedrecord
FROM (
SELECT f.*,
VENDORNUM = (SELECT TOP 1 VENDORNUM FROM INSPOLICYMASTER
WHERE INSURED like '%' + f.Insured_name1 + '%'),
producerid = (SELECT TOP 1 producerid FROM idscomsql.dbo.Producermaster
WHERE producer LIKE '%' + f.Producer_Name + '%')
FROM idscomsql.dbo.feedrecord f
) d
LEFT JOIN Inscertificate c
ON c.VendorNum = d.VendorNum AND c.ProducerId = d.producerid AND c.Issuedate = convert(datetime,d.Issue_Date)
WHERE c.VendorNum IS NULL AND c.ProducerId IS NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2009 at 5:06 am
[font="Arial"]thanks a lot chris....its working .....thanks once again[/font] :):):)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply