CODEWITHSUNDEEP
sqlsql-serversql-server-2008
IT ppl
IT ppl

Reputation: 2647

Insert into query

I am having a temptable which stores the data temporary, in which first field is filled from query and second field is static value

temptable:

Now I do fill first field with following query, but how can I fill both fields at same time?

Following is the query to fill first field only

insert into temptable (RoomID)
select t1.RoomId as RoomID 
from Room t1 
INNER JOIN 
(
      select RoomID, SUM(quantity) as QTY 
      from Room_Item 
      group by RoomID 
      having SUM(Quantity) > 0
 ) t2 on t1.RoomID = t2.RoomID 
 order by RoomID

Upvotes: 1

Views: 342

Answers (4)

Arion
Arion

Reputation: 31239

Maybe something like this:

;WITH CTE
AS
(
    select  
        RoomID, 
        SUM(quantity) OVER(PARTITION BY byRoomID) as QTY
    from    
        Room_Item 
)
insert into temptable 
(
    RoomID, 
    IsAvailable
) 
select  
    Room.RoomId, 
    (
        CASE 
            WHEN CTE.qty IS NOT NULL 
            THEN 1 
            ELSE 0 
        END
    ) AS IsAvailable
from    
    Room 
    LEFT JOIN CTE
        ON t1.RoomId=CTE.RoomID
        AND CTE.QTY>0

Upvotes: 1

Janaki
Janaki

Reputation: 195

insert into temptable (RoomID, IsAvailable) 
select  t1.RoomId as RoomID 
, case when t2.qty IS NOT NULL then 1 ELSE 0 END
from    Room t1 
LEFT OUTER JOIN (
  select  RoomID, SUM(quantity) as QTY 
  from    Room_Item 
  group   byRoomID 
  having  SUM(Quantity) > 0
) t2 on t1.RoomID = t2.RoomID

This will may help you getting result..

Cheers...!!!

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Just pass your static value directly to the SELECT statement

INSERT INTO temptable (RoomID, isavailable)
SELECT t1.RoomId, 1 // static  value
FROM Room t1 
INNER JOIN 
(
    SELECT RoomID, SUM(quantity) AS QTY 
    FROM Room_Item 
    GROUP BY RoomID 
    HAVING SUM(Quantity) > 0
) t2 ON t1.RoomID = t2.RoomID 
ORDER BY RoomID

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

The INNER JOIN is effectively only returning rows when a room is available.

You should

  • use a LEFT JOIN to retain all records
  • use a CASE statement to determine if a room is available or not.

SQL Statement

insert into temptable (RoomID, IsAvailable) 
select  t1.RoomId as RoomID 
        , case when t2.qty IS NOT NULL then 1 ELSE 0 END
from    Room t1 
        LEFT OUTER JOIN (
          select  RoomID, SUM(quantity) as QTY 
          from    Room_Item 
          group   by
                  RoomID 
          having  SUM(Quantity) > 0
        ) t2 on t1.RoomID = t2.RoomID

Upvotes: 3

Related Questions