SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 The Forms of Things Unknown
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 7

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 02/22/2005 :  16:36:34  Show Profile  Reply with Quote
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

Australia
1591 Posts

Posted - 02/22/2005 :  16:51:57  Show Profile  Reply with Quote
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

USA
665 Posts

Posted - 02/22/2005 :  17:10:47  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 02/22/2005 :  17:45:00  Show Profile  Reply with Quote
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

USA
665 Posts

Posted - 02/22/2005 :  17:50:17  Show Profile  Reply with Quote
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

USA
37291 Posts

Posted - 02/22/2005 :  17:52:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
665 Posts

Posted - 02/22/2005 :  17:55:36  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 02/22/2005 :  18:05:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/22/2005 :  18:06:11  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

Sweden
3279 Posts

Posted - 02/22/2005 :  18:08:30  Show Profile  Reply with Quote
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

Edited by - rockmoose on 02/23/2005 11:55:00
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 02/22/2005 :  18:12:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 02/23/2005 :  04:35:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

USA
665 Posts

Posted - 02/23/2005 :  09:41:09  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 02/23/2005 :  11:59:39  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

USA
665 Posts

Posted - 02/23/2005 :  12:23:54  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 02/23/2005 :  12:31:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

USA
665 Posts

Posted - 02/23/2005 :  12:41:38  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 02/23/2005 :  12:59:30  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

USA
665 Posts

Posted - 02/23/2005 :  13:04:19  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 02/23/2005 :  13:07:24  Show Profile  Reply with Quote
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

USA
665 Posts

Posted - 02/23/2005 :  13:21:29  Show Profile  Reply with Quote
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
Page: of 7 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000