Click here to Skip to main content
15,891,908 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

here is my query where i am inserting data to temp table.

SQL
insert into #tmpImportTruckMin(
Month,
Count,
ID,
ContainerSize,
TruckTime,
ContainerOrder
) 
values
(
(
 SELECT (case when @monthnoImportTime = 1 then 'Jan' 
              when @monthnoImportTime = 2 then 'Feb' 
              when @monthnoImportTime = 3 then 'Mar' 
              when @monthnoImportTime= 4 then 'Apr' 
              when @monthnoImportTime = 5 then 'May' 
              when @monthnoImportTime= 6 then 'Jun' 
              when @monthnoImportTime = 7 then 'Jul' 
              when @monthnoImportTime= 8 then 'Aug' 
              when @monthnoImportTime = 9 then 'Sep' 
              when @monthnoImportTime = 10 then 'Oct' 
              when @monthnoImportTime = 11 then 'Nov' 
              when @monthnoImportTime = 12 then 'Dec' end
        ),
 ISNULL(COUNT(GatePassId),0),
 @monthnoImportTime,
 '20FT', 
  AVG(
    CASE 
    WHEN Status = 'In Yard' thenISNULL(DATEDIFF(MINUTE,TimeIn,getdate()),0)  
    when status = 'Left The Yard' then ISNULL(DATEDIFF(MINUTE,TimeIn,TimeOut),0) 
    else '0' end
     ),
1
from tbl_CMS_GatePass 
where MONTH(AddedTime) = @monthnoImportTime 
and YEAR(AddedTime) = @Year 
and DepotID =@DepotID 
and BranchID = @branchID
AND ((ShippingLine IS NULL OR ShippingLine=0) AND (@ShippingLine IS NULL OR @ShippingLine=0) OR ShippingLine= CASE WHEN ((@ShippingLine=0) OR (@ShippingLine IS NULL)) THEN ShippingLine ELSE @ShippingLine END)
and ((Customer IS NULL OR Customer=0) AND (@Client IS NULL OR @Client=0) OR Customer= CASE WHEN ((@Client=0) OR (@Client IS NULL)) THEN Customer ELSE @Client END) and ContainerSize = 2))
Posted
Updated 22-Sep-14 22:03pm
v2
Comments
Oshtri Deka 23-Sep-14 3:22am    
In this state your code is hard to read, how about some basic formatting?

1 solution

You are using the for of INSERT INTO [TABLE] (COL1, COL2... COLn) VALUES(VAL1, VAL2... VALn), but instead of list of values you supply a SELECt statement. That SELECT statement returns more than one rows and that's the cause of the error...
You have two option:
1. Refine the SELECT statement to return single row
2. use INSERT INTO [TABLE] (COL1, COL2... COLn) SELECT VAL1, VAL2... VALn FROM [OTHER TABLE]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900