Alternate to temporary table in azure sql

  • Arjun

    SSC Enthusiast

    Points: 116

    Hi ,

    Currently am using temporary table to add id(identity(1,1)) value to all the records. any alternate solution without temp table.Am moving data from one table to two downstream tables with unique id value using temp table.  Due to this am facing performance issues.

    Table A:

    Name   Age   Email      MobileNo

    Ali         25     gtr@cd    564789003

    Gorg        27     gtyu@cd    432345677

    Temp table: Generating ID(Identity(1,1) for all the records.

    ID   Name   Age   Email      MobileNo

    1     Ali         25     gtr@cd    564789003

    2    Gorg        27     gtyu@cd    432345677

    Downstream tables: Moving data to two tables with same ID value from temp table.

    Table B:

    ID   Name    Age

    1     Ali         25

    2    Gorg        27

    Table C:

    ID   Email      MobileNo

    1     gtr@cd    564789003

    2    gtyu@cd    432345677

    Please suggest any other solution(without temp table) to achieve this.

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

    What exactly are you doing with your code? If you are moving lots of data, this will be hard.

    You don't need a temp table. If you select with row_number(), you'll get a number you can use for one table. For the second, then join back. Is there any key you can use in the original table?

  • scdecade

    SSC-Addicted

    Points: 494

    Why not just create a primary key in table A?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden

    SSC Guru

    Points: 994863

    While you're at it, don't forget to store a date with the age so that you can estimate the actual age in the future.  You might want to  consider further normalization of the data to get to at least 3rd normal form but I don't know what the uses of this data will actually be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

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