Alternate to temporary table in azure sql

  • Arjun

    SSC Enthusiast

    Points: 185

    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: 719155

    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 Eights!

    Points: 807

    Why not just create a primary key in table A?

  • Jeff Moden

    SSC Guru

    Points: 996483

    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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

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

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