merge data help

Discussion in 'ASP General' started by Joey Martin, May 25, 2005.

  1. Joey Martin

    Joey Martin Guest

    I have two tables:
    customermaster (id,name,email,mailinglists)
    example: 1,john doe, , NULL


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

    How can I accomplish this for MS SQL database?



    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, May 25, 2005
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > I have two tables:
    > customermaster (id,name,email,mailinglists)
    > example: 1,john doe, , NULL
    >
    >
    > emailmaster (email,listid)
    > example:
    > 1
    > 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
    >
    > How can I accomplish this for MS SQL database?
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    Ray Costanzo [MVP], May 25, 2005
    #2
    1. Advertising

  3. Joey Martin wrote:
    > I have two tables:
    > customermaster (id,name,email,mailinglists)
    > example: 1,john doe, , NULL
    >
    >
    > emailmaster (email,listid)
    > example:
    > 1
    > 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
    >


    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.
    Bob Barrows [MVP], May 25, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bobby Edward
    Replies:
    1
    Views:
    5,265
    groker
    Apr 7, 2009
  2. c
    Replies:
    9
    Views:
    773
    Seebs
    Apr 26, 2010
  3. alcondor
    Replies:
    0
    Views:
    1,029
    alcondor
    Apr 26, 2010
  4. Dennis Lee Bieber

    Re: Mail Merge from python data

    Dennis Lee Bieber, Nov 3, 2010, in forum: Python
    Replies:
    1
    Views:
    380
    Tim Harig
    Nov 3, 2010
  5. Stephanie S

    FDF Merge users - I could use some help

    Stephanie S, Jul 9, 2003, in forum: ASP General
    Replies:
    1
    Views:
    142
    Tom Kaminski [MVP]
    Jul 9, 2003
Loading...

Share This Page