Post reply

How to Assign values of a column to values of another column and insert these two values in a table

  • May 29, 2008 at 7:25 am

    #380748

    Hi,

    Pls find below the code and the requirement that i need help on

    Code to create tableA and insert values:

    create table tableA(NAME varchar(500),EMAIL varchar(500))

    insert into tableA(NAME,EMAIL) values(denis,denis@gmail.com)

    insert into tableA(NAME,EMAIL) values(richie,richie@gmail.com)

    insert into tableA(NAME,EMAIL) values(tapay,tapay@gmail.com)

    insert into tableA(NAME,EMAIL) values(rose,rose@gmail.com)

    -----------------------------------------------------------------

    Code to create tableB and insert values:

    create table tableB(NAME varchar(500),STATE varchar(500))

    insert into tableB(NAME,STATE) values(ron,alabama)

    insert into tableB(NAME,STATE) values(crown,slovakia)

    insert into tableB(NAME,STATE) values(wang,mississipi)

    insert into tableB(NAME,STATE) values(don,vegas)

    insert into tableB(NAME,STATE) values(sean,detroit)

    -----------------------------------------------------------------

    Code to create tableC:

    create table tableB(NAME varchar(500),STATE varchar(500))

    -----------------------------------------------------------------

    What i require?

    I need to pick a value(randomly) from NAME of tableA and

    assign to it a value(picked randomly) from STATE of tableB and

    insert these values into tableC...

    (As a result all values from NAME of tableA must have an assigned value from STATE of tableB...without duplicates)

    Pls help in constructing the query to do this function in SQL 2000.

    To note: The tables(tableA and tableB) can have 100,000 records

    ...................................................................................................

    A way of performing this in Oracle is something similar to...

    --inserting the result in tableC

    Insert into tableC(NAME,STATE)

    --select random NAME and its randomly assigned STATE

    select NAME,STATE

    from

    --select random value from NAME of tableA

    (select NAME,rownum col1 from (select NAME from tableA order by dbms_random.value)) a ,

    --select random value from STATE of tableB

    (select STATE,rownum col1 from (select STATE from tableB order by dbms_random.value)) b

    where a.col1= b.col1

    --howover duplicate check is not done here

    .....................................................................................................

    As im new to SQL... need some help..:)

    Thanks

    Roy

  • May 29, 2008 at 7:55 am

    #821760

    Making this a 2000-compatible solution makes this a little more complicated. This should work though:

    select *, identity(int,1,1) as RN

    into #A

    from tableA

    order by newid()

    select *, identity(int,1,1) as RN

    into #B

    from tableB

    order by newid()

    Select #B.state, #A.name

    from

    #A

    inner join

    #B on #A.rn=#B.rn

    drop table #A

    drop table #B

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 30, 2008 at 3:01 am

    #822158

    Thanks pal! This served the prupose:)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply