Alternate to temporary table in azure sql

Question

Alternate to temporary table in azure sql

Arjun

SSC Enthusiast

Points: 185
More actions
October 22, 2019 at 6:27 pm

#3691083

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.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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?

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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: 1004340 More actions · Answer 3

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)