CODEWITHSUNDEEP
sqlstored-procedures
smark
smark

Reputation: 255

Insert values from one table into another table in a Stored Procedure on SQL Server

I have a temp table in my Stored Procedure. It looks something like this:

name  NT    AB
aaa   NULL  NULL
bbb   NULL  NULL
...

The other table looks like this:

name  column   value
aaa   NT       2.3
aaa   NT       4.1
aaa   AB       5.2
bbb   CD       3.6
bbb   NT       4.5
...

How can I get the values from the second table into the correct column of the first table in a stored procedure? (name and column in the second table are not unique. For example there can be more aaa NT combinations. In this case the values have to be added up.)

The values for the NT, AB columns are coming from another table. In VB.Net I solved this problem with a loop. I had all the data in a datatable and then filtered for "aaa", "bbb" so that i would get the NT, AB value without having to query the DB all the time. (You can assume that the name column is unique, in reality there are more columns that are unique together)

Upvotes: 1

Views: 2089

Answers (2)

Cyril Gandon
Cyril Gandon

Reputation: 17058

You can have your result with a simple query :

SELECT name, 
       SUM(CASE WHEN column = 'NT' THEN value ELSE 0 END) as NT,
       SUM(CASE WHEN column = 'AB' THEN value ELSE 0 END) as AB
FROM otherTable
GROUP BY name

Upvotes: 0

gbn
gbn

Reputation: 432271

This assumes that:

  • name, column is unique in OtherTable (eg never 2 rows for some name and column combination)
  • You are not interested in column = 'CD'

so (Edited after finding out that SUM is needed)

UPDATE
   t
SET
   NT = O1.TheSum,
   AB = O2.TheSum
FROM
   #temptable T
   LEFT JOIN
   (
    SELECT SUM(value) AS TheSum, name
    FROM OtherTable
    WHERE column = 'NT'
    GROUP BY name
   ) O1 ON T.name = O1.name
   LEFT JOIN
   (
    SELECT SUM(value) AS TheSum, name
    FROM OtherTable
    WHERE column = 'AB'
    GROUP BY name
   ) O2 ON T.name = O2.name

Upvotes: 1

Related Questions