Pivoting Data in SQL and Updting it into Destination Table

Question

I am working on a Store Procedure where I need to update records from from table 1 into table 2.

Table1: contains the following 4 columns


    ----------------------------------------------
    | ID |    TableName   |  ColumnName  | Value |
    ----------------------------------------------
    | 1  |  [UserDetails] | [FinalScore] |  92   |
    | 2  |  [UserDetails] | [FinalScore] |  89   |
    | 3  |  [UserDetails] | [FinalScore] |  65   |
    | 4  |  [UserDetails] | [FinalScore] |  91   |
    | 5  |  [UserDetails] | [FinalScore] |  76   |
    | 1  |[EmployeeDetail]| [ScoreData]  |  0.91 |
    ----------------------------------------------

UserDetails table


    -----------------------------------------------------------
    | UserID |    UserName    |    ContactNo     | FinalScore |
    -----------------------------------------------------------
    |   1    |  John G        | +13288992342     |     0      |
    |   2    |  Leonard J     | +14581232342     |     0      |
    |   3    |  Max R         | +17123992342     |     0      |
    |   4    |  Robert H      | +15688992122     |     0      |
    |   5    |  Jimmy L       | +1328996782      |     0      |
    -----------------------------------------------------------

Need to load all the data from table1 into the corresponding destination table for large amount of data (30,000 to 60,000) records.

Table1 contains the ID (from ID Column in table1) and FinalScore (from ColumnName Column in table1) in the destination table (from TableName Column in table1) where the value (from Value Column in table1) needs to be loaded.

End Result of UserDetails table after SP execution :


    -----------------------------------------------------------
    | UserID |    UserName    |    ContactNo     | FinalScore |
    -----------------------------------------------------------
    |   1    |  John G        | +13288992342     |     92     |
    |   2    |  Leonard J     | +14581232342     |     89     |
    |   3    |  Max R         | +17123992342     |     65     |
    |   4    |  Robert H      | +15688992122     |     91     |
    |   5    |  Jimmy L       | +1328996782      |     76     |
    -----------------------------------------------------------

I am not sure how to load the data from "table1" into destination table (userdetails table) for bulk data as update query in a loop is taking very long time to complete.

score 0 · Answer 1 · answered Apr 15 '20 at 15:33

0

It's not pivot what you're after but a simple join:

select 
  a.userid, a.contactno, b.finalscore
from UserDetails a
join table1 b on a.id = b.id

alternatively, if you want to update the old table without creating a new one, you can update join

SQL update query using joins

answered Apr 15 '20 at 15:33

Matt Kowaleczko

53
6

Hi Matt, The table will hold data thats are not specific to one table. So the tableName column may be of other tables too (UserDetails., EmployeeDetail) – Pradeep K Apr 15 '20 at 16:49

Pivoting Data in SQL and Updting it into Destination Table

1 Answers1