November 18, 2009 at 12:34 pm
Ok below is my newest task assigned to me by my boss, before you curse me about cusors, the SP in the DBs are already FULL of them and my Boss insist that I continue to use cursors. And I am new to T_SQL and have struggled through only a couple cursors with his help.
1.anyway is this really like a select into script but using a cursor?
2. Do you think this task is a little ambiguous about what exactly he wants this script to do or do I just not understand T-SQL enough to get him from the get go.
He wrote: "Adam, write a script that reads the SysWordings table and populates the SysWordings_Optional_EN table with the value form each below column
in the EN_Number when it is not empty or null and the Column name goes in to the EN_Description column. This will add a record for each column not null or empty so you will have to use a cursur as in the Delos.
Here are the "each column below" he is refering to in his task for to me above:
EN_AddInsured,
EN_Alarm,
EN_AnimalLiability,
EN_CovA,
EN_CovB,
EN_CovC,
EN_CovLU,
EN_CovLA,
EN_Cart,
EN_EQ,
EN_Flood,
EN_InflationGuard,
EN_OrdinanceLaw,
EN_PersonalInjury,
EN_PersonalLiabilty,
EN_Rental,
EN_ReplacementCost_CovA,
EN_ReplacementCost_CovC,
EN_Theft,
EN_UnderConstruction,
EN_WaterDamage,
EN_WaterBackUp,
EN_WDR,
VersionDescription,
VersionComments
Below is the table I am to select from ,followed by the table to populate
CREATE TABLE [dbo].[SysWordings](
[ProgramCode] [varchar](7) NOT NULL,
[StateCode] [varchar](2) NOT NULL,
[Ins_CompanyCode] [varchar](2) NOT NULL,
[ContractCode] [varchar](3) NOT NULL,
[EffectiveDate_NB] [smalldatetime] NOT NULL,
[ExpirationDate_NB] [smalldatetime] NOT NULL,
[EffectiveDate_RN] [smalldatetime] NOT NULL,
[ExpirationDate_RN] [smalldatetime] NOT NULL,
[ProgramName] [image] NOT NULL,
[MinimumEarnedClause] [varchar](255) NULL,
[ServiceOfSuit] [varchar](255) NULL,
[PrimaryCorrespondent] [varchar](50) NULL,
[PrimaryCorrespondentAddress] [varchar](50) NULL,
[PrimaryCorrespondentPhone] [varchar](10) NULL,
[PrimaryCorrespondentSSN] [varchar](9) NULL,
[SecondaryCorrespondent] [varchar](50) NULL,
[SecondaryCorrespondentAdd] [varchar](50) NULL,
[StateVerbage] [text] NULL,
[ProgramEndorsement] [varchar](500) NULL,
[DecPageFormNumber] [varchar](30) NULL,
[LineCancellationFee] [varchar](255) NULL,
[CoInsuranceClause] [varchar](50) NULL,
[EN_AddInsured] [varchar](75) NULL,
[EN_Alarm] [varchar](75) NULL,
[EN_AnimalLiability] [varchar](75) NULL CONSTRAINT [DF_SysWordings_EN_AnimalLiability] DEFAULT (''),
[EN_CovA] [varchar](75) NULL,
[EN_CovB] [varchar](75) NULL,
[EN_CovC] [varchar](75) NULL,
[EN_CovLU] [varchar](75) NULL,
[EN_CovLA] [varchar](75) NULL,
[EN_Cart] [varchar](75) NULL,
[EN_EQ] [varchar](75) NULL,
[EN_Flood] [varchar](75) NULL,
[EN_InflationGuard] [varchar](75) NULL,
[EN_OrdinanceLaw] [varchar](75) NULL,
[EN_PersonalInjury] [varchar](75) NULL,
[EN_PersonalLiabilty] [varchar](250) NULL,
[EN_Rental] [varchar](75) NULL,
[EN_ReplacementCost_CovA] [varchar](75) NULL,
[EN_ReplacementCost_CovC] [varchar](75) NULL,
[EN_Theft] [varchar](75) NULL,
[EN_UnderConstruction] [varchar](75) NULL,
[EN_WaterDamage] [varchar](75) NULL,
[EN_WaterBackUp] [varchar](75) NULL,
[EN_WDR] [varchar](75) NULL,
[VersionDescription] [varchar](75) NULL,
[VersionComments] [varchar](100) NULL,
CONSTRAINT [PK_SysWordings] PRIMARY KEY CLUSTERED
(
[ProgramCode] ASC,
[StateCode] ASC,
[Ins_CompanyCode] ASC,
[ContractCode] ASC,
[EffectiveDate_NB] ASC,
[EffectiveDate_RN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
table to populate"
CREATE TABLE [dbo].[SysWordings_Optional_EN](
[ProgramCode] [varchar](7) NOT NULL,
[StateCode] [varchar](2) NOT NULL,
[Ins_CompanyCode] [varchar](2) NOT NULL,
[ContractCode] [varchar](3) NOT NULL,
[EffectiveDate_NB] [smalldatetime] NOT NULL,
[ExpirationDate_NB] [smalldatetime] NOT NULL,
[EffectiveDate_RN] [smalldatetime] NOT NULL,
[ExpirationDate_RN] [smalldatetime] NOT NULL,
[DependentParameter_Name] [varchar](30) NOT NULL,
[DependentParameter_Operator] [varchar](10) NOT NULL,
[DependentParameter_Value] [varchar](255) NOT NULL,
[EN_Description] [varchar](30) NOT NULL,
[EN_Number] [varchar](30) NOT NULL
) ON [PRIMARY]
tanx in Advance!
Adam
November 19, 2009 at 2:36 am
I'm not sure whether I've fully understood you - it seems to me that you can use the UNPIVOT operator here.
Select the columns you need in another table (T1), use unpivot and insert the result in the SysWordings_Optional_EN table. UNPIVOT is described in BOL with many examples.
However something is missing in what I've said - I see that the SysWordings_Optional_EN columns are all NOT NULL and with no default values. You'll have to take care of this also.
November 19, 2009 at 3:00 am
So, let's quickly pass over the terrible design you are being asked to work with...and let's also walk quickly on by the deep nastiness of the new design 🙂
I assume you are being paid for this work (and I'm not) so here's an example of how to do it without a cursor. It's incomplete (I couldn't be bothered with all the typing) but it should help you quite a lot.
DECLARE @T TABLE
(
program_code VARCHAR(7) NOT NULL,
state_code VARCHAR(2) NOT NULL,
contract_code VARCHAR(3) NOT NULL,
en_add_insured VARCHAR(75) NOT NULL,
en_alarm VARCHAR(75) NOT NULL,
en_animal_liability VARCHAR(75) NOT NULL
);
INSERT @T
(
program_code,
state_code,
contract_code,
en_add_insured,
en_alarm,
en_animal_liability
)
VALUES
(
'0000000',
'AA',
'AAA',
'Address',
'Alarm',
'Animal'
);
INSERT @T
(
program_code,
state_code,
contract_code,
en_add_insured,
en_alarm,
en_animal_liability
)
VALUES
(
'1111111',
'BB',
'BBB',
'Address 2',
'',
'Animal 2'
);
SELECT
U.program_code,
U.state_code,
U.contract_code,
U.en_number,
U.en_description
FROM
@T
UNPIVOT
(
en_number
FOR
en_description IN
(
en_add_insured,
en_alarm,
en_animal_liability
)
) U
WHERE
en_number <> '';
Paul
November 19, 2009 at 3:49 am
Paul, it's a shame I'm o lazy...:blush:. Thanks for the detailed example.
Indeed it is a terrible design...and the boss seems to me a "micromanager" - sorry, Adam...
November 19, 2009 at 3:57 am
dmoldovan (11/19/2009)
Paul, it's a shame I'm o lazy...:blush:. Thanks for the detailed example.Indeed it is a terrible design...and the boss seems to me a "micromanager" - sorry, Adam...
Well that's odd. Your reply mentioning UNPIVOT wasn't there when I posted mine!? This site does strange stuff sometimes. There's no way I would have bothered with all that:
(a) usually; and
(b) once UNPIVOT had already been mentioned
I think I might be ill :laugh:
Yeah Adam, best approch is probably to get what you can from this role, then look to move on...soon 😀
November 19, 2009 at 8:41 am
Thanks all for you ehlp. I will procede trying your example. and by the way, this is the most explicit he has been on what the script needs to do.
oFF THE SUBJECT-- i WAS A NATIONAL it TRAINER SQL server admin, Linux, Exchange and WIndows 2k3 until the economy went down and i got laid off after winning best Linux and SQL server trainer.
Now I am getting paid half, been taken advantage od by the econmy by this current comapany by knowing just to have a job at an obser salary for my experience is sucks enough, but becuase Of what i accepted , they treat as such, like 2 years out of college.
I want to master cusrors then show that guy the much more efficient way of making scripts or sps do the same thing w/o cursors. But , I canot look a gift horse in the mouth. I'm thankful to atleast have and IT position at all with so many unemployed skilled IT workers.
We have a huge micomaneger but its not my boss " Directory of Development". He just wants to be able to trouble shoot what he knows well, cursors.
I was told 2 weeks ago after they needed more productivity from me. After I was told to use an Acces module to move pdf's to different folders based on certain criteria and based on 1 criteria, update a record reflecting the customers home insurance has a photo. U do not use VBA in acces to do this. upon live deployment it did not work because the micromanager from hell said he would need access loaded on prod server and he would not so so. ALL that was needed was a few access Dll's copied to the prod server.
. SO I rewrote this in C#, was running perfectly and in real time, not just daily, went to deploy it he said the password in the config file needed to be encrypted. I said lets just not use the sa ,lets use trusted aut, and he mumbled a reason why not. Let the AD hid the pwd. So , here I am rewriting the same program in 2 languages and I am considered unproductive. No I using SSIS , an environemt I know and it can be stroed in the SSIS package store. I am doing this ,becuase I this scenario the pwd is protected. I told him all along to let me use SSIS evewn I am sure it can be down in T-SQL .
so rry to cmplain. Thanks for the help.
November 19, 2009 at 2:13 pm
Sucks to be you 😀
No, seriously, that's tough. Good luck eh.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply