In temporary tables/variables: join multiple rows to table with only 1 row

Question

Hi, I have a small problem of joining a temporary variable with a temporary table. Any input would be much appreciated. I present the problem in the order in which I have tried to solve it.

First of, I have a temporary variable which is created from a select statement. The variable @enhet has 2 rows/observations (in my test file, later it will be many more):

declare @enhet varchar(50)

SELECT @enhet = 
     A.[EnhetsId]
FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
inner join (
            select [EnhetsId], max(SenastUppdaterad) as SenastDatum
            from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
            group by [EnhetsId]
            ) B
on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
WHERE [NyStatus] = 4

Secondly, I want to create a temporary table that combine these two observations (1 variable) with extra variables that are the same for both unique observations of variable @enhet. The aim is to have a table with unique values only for the variable @enhet but the rest is the same.

declare @temp2 table (
    EnhetsId varchar(50), 
    TjanstId Int, 
    Tabell varchar(50),
    Kommentar ntext,
    Uppdaterad datetime
)
insert into @temp2 (
    EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
    values (
        @enhet, 1, 'GR_PS09_1', 'KLAR', getdate())

select * from @temp2

The problem is that when I run the script the output is only 1 row with just the last observation from the @enhet-variable.

Is there anyone who knows what to do? I have tried many things but since I am new to SQL I have failed to write the correct scripts. Can anyone point me in the right direction?

Thanks in advance and best regards! :)

The variable `@enhet` does not have 2 rows/observations -- it is a scalar variable and can only hold 1 value. You can verify by adding `SELECT @enhet` to the end of your first block of code. — mr.Reband, Nov 15 '13 at 18:23
Thanks for your answer. I never thought of looking at SELECT @enhet, since I looked at the output before I added the "@enhet =" string. Thats why I failed to see the problem. Do you have any recommendation on how to make it work? I have tried make the first statement "@enhet" as a table instead of a (scalar) varialble, but still can´t figure out the right script to joint the two temporary tables. — user2995808, Nov 15 '13 at 22:23

score 0 · Accepted Answer · answered Nov 16 '13 at 14:31

0

Use a common table expression to hold the results of your first joined query:

declare @temp2 table (
   EnhetsId varchar(50), 
   TjanstId Int, 
   Tabell varchar(50),
   Kommentar ntext,
   Uppdaterad datetime
);

WITH ENHET_CTE AS 
(
    SELECT A.[EnhetsId]
    FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
    inner join (
             select [EnhetsId], max(SenastUppdaterad) as SenastDatum
             from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
             group by [EnhetsId]
             ) B
    on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
    WHERE [NyStatus] = 4
)

insert into @temp2 
    (EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
SELECT 
    EnhetsId, 1, 'GR_PS09_1', 'KLAR', getdate() 
from ENHET_CTE;

select * from @temp2;

answered Nov 16 '13 at 14:31

mr.Reband

2,434
2
17
22

thanks mr.Reband for the code, It worked perfectly. :) Do you know how to add more conditions on top of the with ** (select..)-statement? I need to add a stored procedure, and given a certain condition I want the "insert into @temp2 " to execute. – user2995808 Nov 19 '13 at 13:38
Not sure what you mean -- you can encapsulate the entire block of code in a stored procedure. What is the 'certain condition'? – mr.Reband Nov 19 '13 at 16:32
I don´t want to change anything in the stored procedure since other programs are dependent upon them, but I want to add the condition that if the output from the SP (that I want to be executed in the code above) is "0" then go ahead with the insert statement (insert into @temp2). – user2995808 Nov 19 '13 at 17:21
Ok, I recommend submitting a new stack overflow question for that request -- there are several ways to accomplish that, none of which will look right buried in the comments here. – mr.Reband Nov 19 '13 at 17:29
actually, I have just done that, I you wanna have a look, here it is: http://stackoverflow.com/questions/20072776/insert-text-into-table-conditional-on-values-returned-from-stored-procedures-an Thanks for your help! – user2995808 Nov 19 '13 at 17:46

In temporary tables/variables: join multiple rows to table with only 1 row

1 Answers1

Linked