Post reply

Insert multiple records from one row

  • October 25, 2010 at 2:06 pm

    #228418

    Hi,

    I have the following record in the source table:

    EmpID Type1 Type1Value Type2 Type2Value Type3 Type3Value

    123456 1 11111 2 22222 3 33333

    Now for each of the Types (1,2,3), I need to insert a record in the source table so it looks like:

    EmpID AssetType AssetValue

    123456 1 11111

    123456 2 22222

    123456 3 33333

    Is this easy to do? How do I do this?

    Thanks in advance

  • October 25, 2010 at 2:22 pm

    #1241903

    There are a number of different ways to do this. Perhaps the easiest is to import it into a staging table, and do a select with Unions to select each column into its own rowset.

    Something like:

    select EmployeeID, Type1, Type1Value

    from MyStagingTable

    union all

    select EmployeeID, Type2, Type2Value

    from MyStagingTable

    ...etc

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 25, 2010 at 2:22 pm

    #1241904

    The easiest way is something like this:

    INSERT INTO Table2 (EmpID, AssetType, AssetValue)

    SELECT EmpID, Type1, Type1Value

    FROM Table1

    UNION ALL

    SELECT EmpID, Type2, Type2Value

    FROM Table1

    UNION ALL

    SELECT EmpID, Type3, Type3Value

    FROM Table1

    James Leeper

    DBA/DB Developer

    WDS

    James Leeper
    Database Administrator
    WDS Global - Americas Region

  • October 25, 2010 at 2:55 pm

    #1241937

    Jinx! 🙂

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 25, 2010 at 2:58 pm

    #1241942

    <---- Buys GSquared a Coke. 🙂

    James Leeper
    Database Administrator
    WDS Global - Americas Region

  • October 25, 2010 at 3:04 pm

    #1241950

    Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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