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

---

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

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

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

---

quote:

---

Originally posted by spirit1

nope. watched maybe 5 episodes of it ...

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

---

quote:

---

Originally posted by spirit1

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

---