Repeating data. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Repeating data.

Author

Topic

lwarunek
Starting Member

22 Posts

Posted - 2008-02-12 : 10:53:13

Hi everybody!

I've got this little problem.
I need to insert data from a table to another table.
The scenario looks as follows:
I've got 'Company' table (no duplicated records there) and 'Contacts' table (one to many relation: for one company there can be more than one contact).
The following statement retrieves the data but it shows me everything, including all contacts and therefore I get duplicating values, e.g. company name.
Is there any way of changing the following query so it works?

INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT Distinct
CI.CompanyID,
CC.ContactID,
--CC.FirstName + ' ' +CC.LastName,
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2

FROM
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID

Kind Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:04:06

Work how?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:11:44

You need to specify your business rules i.e how to handle contact data while making company detalis distinct (taking recent one,earliest one, random one,...)

lwarunek
Starting Member

22 Posts

Posted - 2008-02-12 : 11:24:19

quote:
Originally posted by visakh16

You need to specify your business rules i.e how to handle contact data while making company detalis distinct (taking recent one,earliest one, random one,...)

Ok. Thanks.
It may seem dull but I firstly inserted data without a customer and then updated contactID using join.

Cheers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:35:29

I was asking how you want output for each company. which contacts details want to be returned? While making company unique , you can have only 1 contact detail per company. which one do you want that to be? First inserted one or last inserted one? or are you not concerned about order & just want a random one?

lwarunek
Starting Member

22 Posts

Posted - 2008-02-12 : 11:36:43

quote:
Originally posted by visakh16

I was asking how you want output for each company. which contacts details want to be returned? While making company unique , you can have only 1 contact detail per company. which one do you want that to be? First inserted one or last inserted one? or are you not concerned about order & just want a random one?

It can be last inserted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:41:38

Try this:-

INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT t.CompanyID,
t.ContactID,
t.ContactName,
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2
FROM
(
SELECT 
ROW_NUMBER() OVER(PARTITION BY CI.CompanyID ORDER BY ISNULL(CC.ContactID,0) DESC) AS RowNo,
CI.CompanyID,
CC.ContactID,
CC.FirstName + ' ' +CC.LastName AS ContactName
FROM 
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID
)t
WHERE t.RowNo=1

lwarunek
Starting Member

22 Posts

Posted - 2008-02-12 : 11:44:42

What is the ROW_NUMBER() ?

It seems to be not recognised?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:46:07

And if 2000:-

INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT Distinct
CI.CompanyID,
CC.ContactID,
COALESCE(CC.FirstName,'') + ' ' +COALESCE(CC.LastName,''),
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2

FROM 
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID
left outer join (SELECT MAX(ContactID) AS MaxContact,CompanyID
                 FROM CompanyContacts
                 GROUP BY CompanyID)conmax
ON conmax.MaxContact=CC.ContactID
AND conmax.CompanyID=CC.CompanyID