Please give me a replacement for a Cursor
-
I don't unserstand how you relate the column name to to tables.. can you be more specific?
-
I look up the column name in the Carriers table to get the CarrierID (CarID).
I realized that I was processing EVERY cell (row,column), not just the ones with a '1'. Plus I realized I could shorten it by looking at the columns, not the rows, so I came up with a new Dynamic SQL set-based approach to get all the ProspectID's that have a 1 in the current column. Basically I only iterate the rows in each column that have a 1, as opposed to iterating every column in each row looking for a 1.
This just finished and took 6:40 as opposed to almost 2 hours yesterday:
--------------------------------------------------------------------------
DECLARE @maxcol int
DECLARE @maxid int
DECLARE @curid int
DECLARE @oldid int
DECLARE @ColID int
DECLARE @colname nvarchar(100)
DECLARE @carid int
DECLARE @count float
DECLARE @sql nvarchar(2000)
DECLARE @Parm nvarchar(1000)
DECLARE @cols table(cid int identity, colname nvarchar(100), carid int)
SET NOCOUNT ON
TRUNCATE TABLE APPOINTMENTS
SELECT @maxcol=max(ordinal_position)-2 FROM information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments'
- -- Set a column index (1 to n), and get the column name and carrier ID
- set @ColID=11
While @ColID <=@maxcol BEGIN
insert into @cols
Select column_name, carid FROM information_schema.columns left outer
- join Carriers ON column_name=Carrier
WHERE table_name=N'2004 UIL Prospects Appointments'
set @ColID=@ColID + 1
END
- -- Get all the entries for each carrier and insert appointment if =1
create table #temp (ProspectID int)
Set @ColID=1
While @ColID<@maxcol BEGIN
print 'Col: ' + cast(@colid as varchar(4))
Select @colname = colname, @carid=carid from @cols where cid=@ColID
SET @sql = N'Insert into #temp'
SET @sql = @sql + N' Select ProspectID from [2004 UIL Prospects
- Appointments] WHERE ['+@colname+']=1 ORDER BY ProspectID'
print 'sql: '+@SQL
EXEC dbo.SP_EXECUTESQL @sql
-- #temp contains all entries=1 for that carrier
- SELECT @maxid=count(*) FROM #temp
Print 'Appts: ' + cast(@maxid as varchar(8))
SELECT @maxid=max(ProspectID) FROM #temp
SELECT @curid=min(ProspectID) FROM #temp
SET @oldid=@curid
SET @count = 1
- -- Do an insert for every entry in #temp into Appointments
While @curid<=@maxid BEGIN
INSERT INTO Appointments (ID, CarID) VALUES(@curid, @CarID)
SELECT @curid=min(ProspectID) FROM #temp WHERE ProspectID>@oldid
SET @oldid=@curid
END
truncate table #temp
set @ColID=@ColID+1 -- Next Column
END
drop table #temp
- ------------------------------------------------------------------------
-
-- Populate Carriers
insert into Carriers( CarrierName, ID)
select Column_Name, (Ordinal_Position - 10) as ID
FROM information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments'
and ORdinal_Position between 11 and 102
-- Populate Appointments
insert into Appointments( ID, CarrierID)
select PK_apointment, CarrierID and
YourTable
crossjoin
Carriers
where
1= (case when ID = 1 and [1st Auto] = 1 then 1
when ID = 2 and [Accident Fund] = 1 then 1
when ID = 3 and [ACUITY] = 1 then 1
when ID = 4 and [Allied] = 1 then 1
when ID = 5 and [Amerisure] = 1 then 1
when ID = 6 and [ASI] = 1 then 1
when ID = 7 and [Atlantic Mutual] = 1 then 1
when ID = 8 and [Austin Mutual] = 1 then 1
when ID = 9 and [Badger] = 1 then 1
when ID = 10 and [Barnstable] = 1 then 1
when ID = 11 and [Beacon] = 1 then 1
when ID = 12 and [Bituminous] = 1 then 1
when ID = 13 and [Blue Ridge] = 1 then 1
when ID = 14 and [Brethren Mutual] = 1 then 1
when ID = 15 and [Briarcreek] = 1 then 1
when ID = 16 and [Buckeye] = 1 then 1
when ID = 17 and [Bunker Hill] = 1 then 1
when ID = 18 and [Calif Insurance Group] = 1 then 1
when ID = 19 and [Cameron] = 1 then 1
when ID = 20 and [Casco] = 1 then 1
when ID = 21 and [Central Mutual] = 1 then 1
when ID = 22 and [Chubb] = 1 then 1
when ID = 23 and [Cincinnati Financial] = 1 then 1
when ID = 24 and [C N A] = 1 then 1
when ID = 25 and [Colorado Casualty] = 1 then 1
when ID = 26 and [Columbia] = 1 then 1
when ID = 27 and [Commerce] = 1 then 1
when ID = 28 and [Continental Western] = 1 then 1
when ID = 29 and [crop insurer] = 1 then 1
when ID = 30 and [Cumberland] = 1 then 1
when ID = 31 and [Donegal] = 1 then 1
when ID = 32 and [Encompass] = 1 then 1
when ID = 33 and [Erie] = 1 then 1
when ID = 34 and [Erie and Niagara] = 1 then 1
when ID = 35 and [Everett Cash Mutual] = 1 then 1
when ID = 36 and [FAMI] = 1 then 1
when ID = 37 and [Farmers and Merchanics] = 1 then 1
when ID = 38 and [Farmers Mutual of Nebraska] = 1 then 1
when ID = 39 and [Financial Pacific] = 1 then 1
when ID = 40 and [Finger Lakes] = 1 then 1
when ID = 41 and [Firemans Fund] = 1 then 1
when ID = 42 and [Foremost] = 1 then 1
when ID = 43 and [Fremont Insurance] = 1 then 1
when ID = 44 and [General Casualty] = 1 then 1
when ID = 45 and [GMAC] = 1 then 1
when ID = 46 and [GuideOne] = 1 then 1
when ID = 47 and [Hanover] = 1 then 1
when ID = 48 and [Harleysville] = 1 then 1
when ID = 49 and [Hartford] = 1 then 1
when ID = 50 and [Hastings Mutual] = 1 then 1
when ID = 51 and [Hawaii Employers] = 1 then 1
when ID = 52 and [IIABA] = 1 then 1
when ID = 53 and [Iowa Mutual] = 1 then 1
when ID = 54 and [Kansas Mutual] = 1 then 1
when ID = 55 and [Lebananon] = 1 then 1
when ID = 56 and [Liberty Northwest] = 1 then 1
when ID = 57 and [Madison Mutual] = 1 then 1
when ID = 58 and [Marysville Mutual] = 1 then 1
when ID = 59 and [MD Injured Works fund] = 1 then 1
when ID = 60 and [Merchants] = 1 then 1
when ID = 61 and [Mercury] = 1 then 1
when ID = 62 and [Miami Mutual] = 1 then 1
when ID = 63 and [MICOA] = 1 then 1
when ID = 64 and [Midwest Family Mutual] = 1 then 1
when ID = 65 and [Montgomery Insurance] = 1 then 1
when ID = 66 and [MSA] = 1 then 1
when ID = 67 and [Mutual Fire] = 1 then 1
when ID = 68 and [NLC] = 1 then 1
when ID = 69 and [Northern Neck] = 1 then 1
when ID = 70 and [Ohio Casualty] = 1 then 1
when ID = 71 and [OneBeacon] = 1 then 1
when ID = 72 and [Oregon Mutual] = 1 then 1
when ID = 73 and [Peninsula] = 1 then 1
when ID = 74 and [Penn National] = 1 then 1
when ID = 75 and [Pioneer State] = 1 then 1
when ID = 76 and [PRAC] = 1 then 1
when ID = 77 and [Preferred Mutual] = 1 then 1
when ID = 78 and [PROG] = 1 then 1
when ID = 79 and [Rockford Mutual] = 1 then 1
when ID = 80 and [Rockwood Casualty] = 1 then 1
when ID = 81 and [Safeco] = 1 then 1
when ID = 82 and [Safety] = 1 then 1
when ID = 83 and [SAIF] = 1 then 1
when ID = 84 and [Secura] = 1 then 1
when ID = 85 and [Selective] = 1 then 1
when ID = 86 and [Southern Family] = 1 then 1
when ID = 87 and [Southern Mutual] = 1 then 1
when ID = 88 and [St Paul] = 1 then 1
when ID = 89 and [State Auto] = 1 then 1
when ID = 90 and [Superior] = 1 then 1
when ID = 91 and [The Columbus OH Grange] = 1 then 1
when ID = 92 and [Travelers] = 1 then 1
when ID = 93 and [Tuscarora-Wayne] = 1 then 1
when ID = 94 and [Unigard] = 1 then 1
when ID = 95 and [Unitrin] = 1 then 1
when ID = 96 and [West Bend] = 1 then 1
when ID = 97 and [Western Reserve] = 1 then 1
when ID = 98 and [Westfield] = 1 then 1
when ID = 99 and [Wilson Mutual] = 1 then 1
when ID = 100 and [Windsor] = 1 then 1
when ID = 101 and [Wisconsin Mutual] = 1 then 1
when ID = 102 and [Wolverine] = 1 then 1
else 0 end)
* Noel -
Noeld -
Carriers is already populated. It's a fixed table. The only time it's updated is when a new Carrier is introduced in the "UIL Prospects Appointments" table (The "2004" in the name is unfortunate. The original developer was obviously a moron since the table is updated a couple of times each year, he even built in code to do that. It was imported from an Excel spreadsheet and I think he just took the default name. I'd have called it "Prospects").
The updates to the Prospects table that results in new carriers would require recoding of your gigundo Select statement. Since the customer has the capability to import a new spreadsheet into the table (and thereby change the schema -- double ewwww!) this becomes problematic. We've dealt with that in terms of ensuring the Carriers table gets updated based on the columns in Prospects, but rebuilding the Appointments table has always been a pain.
The whole thing runs as a DTS Job so now that I have it down to 6 minutes from 2 hours and I still have the flexibility of not being hard-coded to the schema I think I'll leave well enough alone.
It is a good example of a Set-Based answer to the problem, though.
-
If the schema changes you can generate the SQL dynamically.
BTW: you are HARD CODING COLUMN POSITIONS too ( set @ColID=11 )
I bet my query BLASTS the heck out of the while loops!
* Noel -
Not even worth trying to compare.. gonna be too depressing
. -
"If the schema changes you can generate the SQL dynamically." Oy... I suppose I could, I'll have to think on that one. That's a lot of dynamic sql.
"BTW: you are HARD CODING COLUMN POSITIONS too ( set @ColID=11 )" Yes, BUT the schema changes are only after column 10. The first ten columns are fixed info (agency name, contact, address, etc). Cols 11 through nCols-2 are the variable part of the schema.
"I bet my query BLASTS the heck out of the while loops!" Could be. If I have some time maybe I'll code up a test and try it.
-
>>Oy... I suppose I could, I'll have to think on that one. That's a lot of dynamic sql. <<
You mean this ?
declare @STR varchar(8000)
@STR = 'insert into Appointments( ID, CarrierID)
select PK_apointment, CarrierID and
YourTable
crossjoin
Carriers
where
1= (case '
select @STR = @STR + 'When ID = ' cast(columnID - 10 as varchar(4)) + ' and [' + column_name + '] = 1 then 1 ' + chr(13) + chr(10)
from
information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments'
and ORdinal_Position between 11 and (select max(columnID)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments') -2
set @STR = @STR + ' else 0 end)'
exec (@str)
* Noel -
Hmmm... 6:36 v 00:26. There's a difference in the results between the two of about 1,000 entries in the Appointments table. I'll have to see if I can figure out where that's coming from. Fast doesn't help if it's not right. Maybe I'll tackle generating the Where clause and using ExecuteSQL so I know I'm getting the right Carrier ID AND Column name...
You were right, I was wrong...
-
You mean this ?
declare @STR varchar(8000)
@STR = 'insert into Appointments( ID, CarrierID)
select PK_apointment, CarrierID and
YourTable
crossjoin
Carriers
where
1= (case '
select @STR = @STR + 'When ID = ' cast(columnID - 10 as varchar(4)) + ' and [' + column_name + '] = 1 then 1 ' + chr(13) + chr(10)
from
information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments'
and ORdinal_Position between 11 and (select max(columnID)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments') -2
set @STR = @STR + ' else 0 end)'
exec (@str)
Uhh... Yeah. That. Thanks.
-
Just watch out for the limit of 8000 characters which at the pace you are going you may get there in no time
Not that it is impossible because you can always use two variables and run the exec like : exec (@str1 + @str2) but it needs a bit of recodingGood luck!
* Noel -
OK. The code stops at Carrier #88:
insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments] P cross join Carriers C where 1= (case
When ID = 1 and [1st Auto] = 1 then 1
When ID = 2 and [Accident Fund] = 1 then 1
When ID = 3 and [ACUITY] = 1 then 1
When ID = 4 and [Allied] = 1 then 1
When ID = 5 and [Amerisure] = 1 then 1
When ID = 6 and [ASI] = 1 then 1
When ID = 7 and [Atlantic Mutual] = 1 then 1
When ID = 8 and [Austin Mutual] = 1 then 1
When ID = 9 and [Badger] = 1 then 1
When ID = 10 and [Barnstable] = 1 then 1
When ID = 11 and [Beacon] = 1 then 1
When ID = 12 and [Bituminous] = 1 then 1
When ID = 13 and [Blue Ridge] = 1 then 1
When ID = 14 and [Brethren Mutual] = 1 then 1
When ID = 15 and [Briarcreek] = 1 then 1
When ID = 16 and [Buckeye] = 1 then 1
When ID = 17 and [Bunker Hill] = 1 then 1
When ID = 18 and [Calif Insurance Group] = 1 then 1
When ID = 19 and [Cameron] = 1 then 1
When ID = 20 and [Casco] = 1 then 1
When ID = 21 and [Central Mutual] = 1 then 1
When ID = 22 and [Chubb] = 1 then 1
When ID = 23 and [Cincinnati Financial] = 1 then 1
When ID = 24 and [C N A] = 1 then 1
When ID = 25 and [Colorado Casualty] = 1 then 1
When ID = 26 and [Columbia] = 1 then 1
When ID = 27 and [Commerce] = 1 then 1
When ID = 28 and [Continental Western] = 1 then 1
When ID = 29 and [crop insurer] = 1 then 1
When ID = 30 and [Cumberland] = 1 then 1
When ID = 31 and [Donegal] = 1 then 1
When ID = 32 and [Encompass] = 1 then 1
When ID = 33 and [Erie] = 1 then 1
When ID = 34 and [Erie and Niagara] = 1 then 1
When ID = 35 and [Everett Cash Mutual] = 1 then 1
When ID = 36 and [FAMI] = 1 then 1
When ID = 37 and [Farmers and Merchanics] = 1 then 1
When ID = 38 and [Farmers Mutual of Nebraska] = 1 then 1
When ID = 39 and [Financial Pacific] = 1 then 1
When ID = 40 and [Finger Lakes] = 1 then 1
When ID = 41 and [Firemans Fund] = 1 then 1
When ID = 42 and [Foremost] = 1 then 1
When ID = 43 and [Fremont Insurance] = 1 then 1
When ID = 44 and [General Casualty] = 1 then 1
When ID = 45 and [GMAC] = 1 then 1
When ID = 46 and [GuideOne] = 1 then 1
When ID = 47 and [Hanover] = 1 then 1
When ID = 48 and [Harleysville] = 1 then 1
When ID = 49 and [Hartford] = 1 then 1
When ID = 50 and [Hastings Mutual] = 1 then 1
When ID = 51 and [Hawaii Employers] = 1 then 1
When ID = 52 and [IIABA] = 1 then 1
When ID = 53 and [Iowa Mutual] = 1 then 1
When ID = 54 and [Kansas Mutual] = 1 then 1
When ID = 55 and [Lebananon] = 1 then 1
When ID = 56 and [Liberty Northwest] = 1 then 1
When ID = 57 and [Madison Mutual] = 1 then 1
When ID = 58 and [Marysville Mutual] = 1 then 1
When ID = 59 and [MD Injured Works fund] = 1 then 1
When ID = 60 and [Merchants] = 1 then 1
When ID = 61 and [Mercury] = 1 then 1
When ID = 62 and [Miami Mutual] = 1 then 1
When ID = 63 and [MICOA] = 1 then 1
When ID = 64 and [Midwest Family Mutual] = 1 then 1
When ID = 65 and [Montgomery Insurance] = 1 then 1
When ID = 66 and [MSA] = 1 then 1
When ID = 67 and [Mutual Fire] = 1 then 1
When ID = 68 and [NLC] = 1 then 1
When ID = 69 and [Northern Neck] = 1 then 1
When ID = 70 and [Ohio Casualty] = 1 then 1
When ID = 71 and [OneBeacon] = 1 then 1
When ID = 72 and [Oregon Mutual] = 1 then 1
When ID = 73 and [Peninsula] = 1 then 1
When ID = 74 and [Penn National] = 1 then 1
When ID = 75 and [Pioneer State] = 1 then 1
When ID = 76 and [PRAC] = 1 then 1
When ID = 77 and [Preferred Mutual] = 1 then 1
When ID = 78 and [PROG] = 1 then 1
When ID = 79 and [Rockford Mutual] = 1 then 1
When ID = 80 and [Rockwood Casualty] = 1 then 1
When ID = 81 and [Safeco] = 1 then 1
When ID = 82 and [Safety] = 1 then 1
When ID = 83 and [SAIF] = 1 then 1
When ID = 84 and [Secura] = 1 then 1
When ID = 85 and [Selective] = 1 then 1
When ID = 86 and [Southern Family] = 1 then 1
When ID = 87 and [Southern Mutual] = 1 then 1
When ID = 88 and [St Paul] = 1 else 0 end)
The length of the final string is 4012. The final code is:
declare @STR varchar(8000)
SET @STR = 'insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments] P cross join Carriers C where 1= (case '+ char(13) + char(10)
select @STR = @STR + 'When ID = ' + cast(Ordinal_Position-10 as varchar(6)) + ' and [' + column_name + '] = 1 then 1 ' + char(13) + char(10)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments' and
Ordinal_Position between 11 and (select max(Ordinal_Position)-2
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments')
ORDER BY Ordinal_Position
set @STR = @STR + ' else 0 end)'
print @STR
print 'len: ' + cast(len(@str) as varchar(8))
I had to make some minor adjustments. I ran the (select max(Ordinal_Position)-2
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments') subquery independently and it returns "112". I tried hard-coding 112 ('between 11 and 112') in the main query and it still stopped at #88. Could it have something to do with string length? @STR is varchar(8000). I don't get it.
-
It has something to do with string length. When I changed the code to:
declare @STR varchar(8000)
SET @STR = 'insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments] P cross join Carriers C where 1= (case '+ char(13) + char(10)
select @STR = @STR + 'When C.CarID = ' + cast(Ordinal_Position-10 as varchar(6)) + ' and [' + column_name + '] = 1 then 1 ' + char(13) + char(10)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments' and
Ordinal_Position between 11 and (select max(Ordinal_Position)-2
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments')
ORDER BY Ordinal_Position
set @STR = @STR + ' else 0 end)'
(I forgot that "ID" should have been "C.CarID") it only got up to #79 and the string length was still 4012.
-
Don't know if there's a more elegant solution, but this works:
declare @str1 varchar(8000)
declare @str2 varchar(8000)
SET @str1 = 'insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments] P cross join Carriers C where 1= (case '+ char(13) + char(10)
select @str1 = @str1 + 'When C.CarID=' + cast(Ordinal_Position-10 as varchar(6)) + ' and [' + column_name + ']=1 then 1 ' + char(13) + char(10)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments' and
Ordinal_Position between 11 and 60
ORDER BY Ordinal_Position
SET @str2 = ''
select @str2 = @str2 + 'When C.CarID=' + cast(Ordinal_Position-10 as varchar(6)) + ' and [' + column_name + ']=1 then 1 ' + char(13) + char(10)
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments' and
Ordinal_Position between 61 and (select max(Ordinal_Position)-2
from information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments')
ORDER BY Ordinal_Position
set @str1 = @str1 + @str2 + ' else 0 end)'
print @str1
print 'len: ' + cast(len(@str1) as varchar(8))
Final length = 4,744
Now to see if it runs.
-
The above is essentially the final code. It ran great (26 seconds) and the discrepency in the results vs my original code was a bug in my original code.
Kudos noeld. Of course I would have saved myself some heartache and a few long posts had I noticed your message at the top of this page on string length (duh!). I was able to figure that one out myself though...
Thanks bunches guys. I learned a lot from this one.
Viewing 15 posts - 31 through 45 (of 70 total)
You must be logged in to reply to this topic. Login to reply