Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > merge data help

Reply
Thread Tools

merge data help

 
 
Joey Martin
Guest
Posts: n/a
 
      05-25-2005
I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, http://www.velocityreviews.com/forums/(E-Mail Removed), NULL


emailmaster (email,listid)
example:
(E-Mail Removed) 1
(E-Mail Removed) 8


I want to get rid of the emailmaster table and merge the listids, into
the mailinglists field inside customermaster so that I will have
something like 1,8,14,25,99 in the mailinglists field for the email
address (E-Mail Removed)

How can I accomplish this for MS SQL database?



*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      05-25-2005
Why do you want to do that? That is not a good database design by any
means, in my opinion. Actually, what I think you should do is keep your
emailmaster table, but drop the email column and replace it with a
customerid column that holds the ID column from the customermaster table.

Ray at work

"Joey Martin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have two tables:
> customermaster (id,name,email,mailinglists)
> example: 1,john doe, (E-Mail Removed), NULL
>
>
> emailmaster (email,listid)
> example:
> (E-Mail Removed) 1
> (E-Mail Removed) 8
>
>
> I want to get rid of the emailmaster table and merge the listids, into
> the mailinglists field inside customermaster so that I will have
> something like 1,8,14,25,99 in the mailinglists field for the email
> address (E-Mail Removed)
>
> How can I accomplish this for MS SQL database?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-25-2005
Joey Martin wrote:
> I have two tables:
> customermaster (id,name,email,mailinglists)
> example: 1,john doe, (E-Mail Removed), NULL
>
>
> emailmaster (email,listid)
> example:
> (E-Mail Removed) 1
> (E-Mail Removed) 8
>


?
This does not seem to be an "emailmaster". It seems to be more of a "bridge"
table to resolve a many-to-many link between customermaster and perhaps a
table called listmaster...
I'm wondering why email was used instead of the id from the customermaster
....

What are the datatypes? is listid numeric or character?

>
> I want to get rid of the emailmaster table and merge the listids, into
> the mailinglists field inside customermaster so that I will have
> something like 1,8,14,25,99 in the mailinglists field for the email
> address (E-Mail Removed)
>


This is a HORRIBLE idea. By storing multiple pieces of data in a single
column, you are reducing the ability of the databse to maintain the
integrity of the data, as well as making it much more difficult to write
some queries to retrieve data. Maintaining this data will be a nightmare as
well.

Have you truly thought this through? Run it by someone who knows something
about database design? You really should consult somebody ...


> How can I accomplish this for MS SQL database?
>

What version? If 2000, you can create a UDF (I've taken a guess as to the
datatypes and sizes required):

CREATE FUNCTION dbo.ConcatRows (
@email varchar(80))
RETURNS varchar(500)
AS
BEGIN
DECLARE @val varchar(500)
SET @val=''
SELECT @val= @val +
CASE @val WHEN '' THEN '' ELSE ',' END
+ CAST(listid as varchar(10))
FROM emailmaster
WHERE email = @email

RETURN @val
END

Then, you can use the UDF in an update statment, like this:

UPDATE customermaster
SET mailinglists = dbo.ConcatRows(email)


If SQL 7, you are going to need to do some cursor work, either in T-SQL, or
using an ADO recordset from ASP.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Mail Merge from python data Dennis Lee Bieber Python 1 11-03-2010 05:20 AM
Merge Sort on linked list..my code is almost done..please help me onit c C Programming 9 04-26-2010 04:23 PM
Re: CSV merge data via HTTP: redundant downloads? Doug Robbins - Word MVP Computer Support 18 02-27-2010 05:10 PM
Help: ILMerge.Merge Error - Duplicate type found in assembly Bobby Edward ASP .Net 1 04-07-2009 08:40 PM
Word 2003 Help -- Mail Merge blackX Microsoft Certification 0 07-21-2008 05:50 PM



Advertisments