Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 The Forms of Things Unknown

Author  Topic 

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 16:36:34
I have in the past used this particular piece of code in debating the cons and cons of cursors. However, I have been advised that this vicious repetitious monster may well grow from 7 to 42! So I'm asking....

Is there any other way to slice this down to a manageable beast?

Here's the monster:

if exists
(select * from XERXES.dbo.sysobjects where id = object_id(N'[XERXES].[dbo].[tab_COBMembr_TEST]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [XERXES].[dbo].[tab_COBMembr_TEST]

CREATE TABLE [XERXES].[dbo].[tab_COBMembr_TEST]
(
[Name] varchar (30) NULL,
[MemberNum] varchar (16) NULL,
[COBName] varchar (30) NULL,
[COBPolicy#] varchar (10) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL,
[COBPriority] varchar (1) NULL,
[COBID] varchar (10) NULL,
[MedPrimary] varchar (1) NULL,
[COBSequence] varchar (6) NULL
)

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB1Name, COB1Policy#, COB1EffDt, COB1TermDt, COB1Priority, COB1ID,
' ' as MedPrimary, 'COB1ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB1ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB1Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB2Name, COB2Policy#, COB2EffDt, COB2TermDt, COB2Priority, COB2ID,
' ' as MedPrimary, 'COB2ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB2ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB2Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB3Name, COB3Policy#, COB3EffDt, COB3TermDt, COB3Priority, COB3ID,
' ' as MedPrimary, 'COB3ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB3ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB3Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB4Name, COB4Policy#, COB4EffDt, COB4TermDt, COB4Priority, COB4ID,
' ' as MedPrimary, 'COB4ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB4ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB4Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB5Name, COB5Policy#, COB5EffDt, COB5TermDt, COB5Priority, COB5ID,
' ' as MedPrimary, 'COB5ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB5ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB5Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB6Name, COB6Policy#, COB6EffDt, COB6TermDt, COB6Priority, COB6ID,
' ' as MedPrimary, 'COB6ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB6ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB6Priority) = 'P'

INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate,
TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT MemberName, MemberID,
COB7Name, COB7Policy#, COB7EffDt, COB7TermDt, COB7Priority, COB7ID,
' ' as MedPrimary, 'COB7ID' as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB7ID NOT in (' ','000000990','000000800','000001016','000001013') and UPPER(COB7Priority) = 'P'

GO



Thanks for whatever sage advice you can impart!


Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-22 : 16:51:57
Xerxes,

That looks OK to me.. You could UNION the SELECTs into a single INSERT but that is your call.

I won't ask too many questions about the table with the columns labelled COB?... Where ? is between 0 and 42.. it looks nasty....

I take it the cursor version replaces the ? with numbers.. I can see why that is tempting

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 17:10:47
DavidM,

Thanks for looking at this. The 7 to 42 means that currently I'm doing inserts with these seven GROUPS:

MemberName, MemberID,
COB1Name, COB1Policy#, COB1EffDt, COB1TermDt, COB1Priority, COB1ID,
' ' as MedPrimary, 'COB1ID' as COBSequence

MemberName, MemberID,
COB2Name, COB2Policy#, COB2EffDt, COB2TermDt, COB2Priority, COB2ID,
' ' as MedPrimary, 'COB2ID' as COBSequence

-------3 4 5 6 go here ----

MemberName, MemberID,
COB7Name, COB7Policy#, COB7EffDt, COB7TermDt, COB7Priority, COB7ID,
' ' as MedPrimary, 'COB7ID' as COBSequence


I don't relish repeating this another 35 times

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-22 : 17:45:00
It looks like the problem you have is that your input data is stored in a denormalized table with repeating groups of data.

Problems like this are why you normalize tables. If you can't change the design of the input table, there isn't much you can do to make this better.




Codo Ergo Sum
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 17:50:17
MVJ,

It's a dBase file that I'm DTS-ing to SQL and amending.

Perhaps a cursor would be the way to go here.

Thanks for taking a gander at this

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 17:52:21
Xerxes, but why aren't you normalizing on the way into SQL Server? Just because your dBase file is denormalized, that doesn't mean that the SQL Server database has to be as well. You can transform data via DTS. What's the point of upgrading this to SQL Server if you are going to keep the same dBase design?

Tara
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-22 : 17:55:36
quote:
Originally posted by tduggan

Xerxes, but why aren't you normalizing on the way into SQL Server? Just because your dBase file is denormalized, that doesn't mean that the SQL Server database has to be as well. You can transform data via DTS. What's the point of upgrading this to SQL Server if you are going to keep the same dBase design?

Tara



Well, Tara....I guess it's just that I'm not entirely sure what part of the trainwreck to start at.I don't know how else I'd set up that COB sequence anyways. I'm open to suggestions.

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-22 : 18:05:04
Maybe, if you managed to redesign [tab_ELCombo] it would make your problems go away ?!
This table has now 7 repating groups, and might grow to 42...
I don't know exactly your situation, but maybe some design work now will save more work down the road.

rockmoose
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-22 : 18:06:11
you could use sql to generate the rows. Would you care if there are 42 queries if you don't have to type them all??


Set nocount on
Declare @numbers table (n int)
Insert Into @numbers
Select n = n1+n2+n3+n4+n5+n6+n7+n8
From
(Select n1=0 Union All Select 1) n1,
(Select n2=0 Union All Select 2) n2,
(Select n3=0 Union All Select 4) n3,
(Select n4=0 Union All Select 8) n4,
(Select n5=0 Union All Select 16) n5,
(Select n6=0 Union All Select 32) n6,
(Select n7=0 Union All Select 64) n7,
(Select n8=0 Union All Select 128) n8


Select
'INSERT INTO [XERXES].[dbo].[tab_COBMembr_TEST](Name, MemberNum, COBName, COBPolicy#, StartDate, TermDate, COBPriority, COBID, MedPrimary, COBSequence)
SELECT
MemberName,
MemberID,
COB'+convert(varchar,n)+'Name,
COB'+convert(varchar,n)+'Policy#,
COB'+convert(varchar,n)+'EffDt,
COB'+convert(varchar,n)+'TermDt,
COB'+convert(varchar,n)+'Priority,
COB'+convert(varchar,n)+'ID,
'' '' as MedPrimary,
COB'+convert(varchar,n)+'ID as COBSequence
FROM [XERXES].[dbo].[tab_ELCombo]
WHERE COB7ID NOT in ('' '',''000000990'',''000000800'',''000001016'',''000001013'') and UPPER(COB7Priority) = ''P''

'
From @numbers

Set nocount off


Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-22 : 18:08:30
Code Generation, Way to Go !

joke:
NG of copy&paste

Edit:
Clarification:
Next Generation of copy&Paste Programming.
Ok, the lame factor is so high that it even hurts to think of this as a joke.

One differentiator between an engineer(programmer) and a programmer(non-engineer), is that the engineer will think about the program that writes the program.
So that he won't have to write the code himself.
There is usually a breakeven point somewhere when the engineering approach will be more effective than the manual repetive approach of actually writing the code oneself.

rockmoose
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-22 : 18:12:58
I don't know what you are doing with COB, but here's an example of going from denormalized to normalized:



SET NOCOUNT ON

--denormalized
CREATE TABLE Person
(
PersonID int NOT NULL,
Attribute1 varchar(50) NOT NULL,
Attribute2 varchar(50) NOT NULL,
Attribute3 varchar(50) NOT NULL
)

INSERT INTO Person VALUES(1, 'SomeValue1', 'SomeValue2', 'SomeValue3')
INSERT INTO Person VALUES(2, 'SomeValue4', 'SomeValue2', 'SomeValue9')

SELECT * FROM Person

DROP TABLE Person

GO

--normalized
CREATE TABLE Person
(
PersonID int NOT NULL
)

CREATE TABLE PersonAttribute
(
PersonAttributeID int NOT NULL,
PersonAttribute varchar(50) NOT NULL
)

CREATE TABLE PersonAttributeVal
(
PersonID int NOT NULL,
PersonAttributeID int NOT NULL,
PersonAttributeVal varchar(50) NOT NULL
)

INSERT INTO Person VALUES(1)
INSERT INTO Person VALUES(2)

INSERT INTO PersonAttribute VALUES(1, 'Attribute1')
INSERT INTO PersonAttribute VALUES(2, 'Attribute2')
INSERT INTO PersonAttribute VALUES(3, 'Attribute3')

INSERT INTO PersonAttributeVal VALUES(1, 1, 'SomeValue1')
INSERT INTO PersonAttributeVal VALUES(1, 2, 'SomeValue2')
INSERT INTO PersonAttributeVal VALUES(1, 3, 'SomeValue3')
INSERT INTO PersonAttributeVal VALUES(2, 1, 'SomeValue4')
INSERT INTO PersonAttributeVal VALUES(2, 2, 'SomeValue2')
INSERT INTO PersonAttributeVal VALUES(2, 3, 'SomeValue9')

SELECT p.PersonID, pav.PersonAttributeVal
FROM Person p
INNER JOIN PersonAttributeVal pav
ON p.PersonID = pav.PersonID
INNER JOIN PersonAttribute pa
ON pav.PersonAttributeID = pa.PersonAttributeID
WHERE pa.PersonAttribute = 'Attribute1'

DROP TABLE Person
DROP TABLE PersonAttribute
DROP TABLE PersonAttributeVal



We now have 3 tables instead of one. One table for the Person, one for the Attributes that a Person can have, and a third to store the values of the attributes for that person. It's a rather lame example, but it should help you understand what you need to do.

Tara
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 04:35:17
yup normalization is the way to go here....
i guess corey's inserts are as good a solution as any...
you might use xp_execresultset for executing all of those inserts at once,
but you might as well run then by hand if you need to.

OR... you can use a cursor ....
and just when you thought you beat them

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 09:41:09
WOW! The ideas you folks come up with is simply STAGGERING! Thanks for all the input on this, I have a lot here to consider!

To SQL Warrior Queen: Unfortunately, EL_Combo is untouchable, that's why I'm working around it. But I just might DTS it, take it apart and reassemble as you suggested!

To the Moose: I didn't get the joke: NG of copy & paste.? Must be another SQL concept I missed

To Corey: What a cool example! I'll try it.

To Mladen: Yeah, I was really hoping I'd stay out of cursor-land

Gee, I love this site!

SELECT THANKS = (* 10^6)!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 11:59:39
so moose are you an engineer(programmer) and a programmer(non-engineer)?

NG reminded of the The next Generation <- yup star trek...


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 12:23:54
Mladen,
If you're discussing ST TNG, you should note that the name of my topic was taken from an old Outer Limits episode .



Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 12:31:25
didn't watch much of outer limit... wasn't very popular here...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 12:41:38
quote:
Originally posted by spirit1

didn't watch much of outer limit... wasn't very popular here...

Go with the flow & have fun! Else fight the flow



Really? You're kidding, right?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 12:59:30
nope. watched maybe 5 episodes of it ...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 13:04:19
quote:
Originally posted by spirit1

nope. watched maybe 5 episodes of it ...

Go with the flow & have fun! Else fight the flow



I would think as a trekker (like myself) that you'd like it (Shatner and Nimoy are in episodes "Cold Hands, Warm Heart" & "I, Robot" respectively). Stephen King says "The Outer Limits was the best program of its type ever to run on network TV!" Stevie's right!

Can't believe you only saw five.

They're on DVD....or check out the library!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-23 : 13:07:24
quote:
Originally posted by spirit1

so moose are you an engineer(programmer) and a programmer(non-engineer)?


As always there are extremes either way.
I've seen programmrs go:
"oh dude, it compiles, uh good..."
And the other way:
"ok, how do we solve this, what is the best algorithm?, can it be done in another way?, hmm, have to test some stuff to find the optimal solution..., what if this happens---or this?, this was cool,,, wonder what theorethical foundation this has?, better do some reading... ... ...Ok now I can start writing the program!"

The form of things unknown...
Almost like a Zen thing, Great title!, and I really enjoy ST.
But I can't recall the title etc.. of any episodes as such, I just watch them when I get the chance

rockmoose
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-23 : 13:21:29
So how about the Outer Limits, moose? Did you see them?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!
Go to Top of Page
    Next Page

- Advertisement -