merge data help

[Joey Martin]

I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, (e-mail address removed), NULL


emailmaster (email,listid)
example:
(e-mail address removed) 1
(e-mail address 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 address removed)

How can I accomplish this for MS SQL database?

 

[Ray Costanzo [MVP]]

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

 

[Bob Barrows [MVP]]

I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, (e-mail address removed), NULL


emailmaster (email,listid)
example:
(e-mail address removed) 1
(e-mail address 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 address 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