asked
Subquery returned more than 1 value Sql
i fix the erro to give top 1 records.Shipmentid, But it just insert one row . How i can do to insert if i have 20 or 30 or many rows. Please some advise. DECLARE @shipID bigint Set @shipID =(SELECT TOP 1 Records.shipmentid FROM Records WHERERecords.[vend]='SPD' and NOT EXISTS (SELECT tablecheck.ObjectID FROM tablecheck WHERE (Records.shipmentid = tablecheck.ObjectID) AND (tablecheck.Operation = 'Sent to SpeeDee')) --SELECT * --from tablecheck --WHERE (Records.shipmentid = tablecheck.ObjectID) ) ) if @shipID is not null begin INSERT INTO [tablecheck] ([ObjectType] ,[Operation] ,[ObjectID] ,[Date] ,[User]) VALUES ('Consigment' ,'Sent to SpeeDee' , @shipID , getdATE() , @as_username ) return 1 end else return 0 end
I have not tested, but it looks like you could rewrite the entire thing as a single insert statement: INSERT INTO [tablecheck] ([ObjectType] ,[Operation] ,[ObjectID] ,[Date] ,[User]) select distinct 'Consignment', 'Sent to Speedee', records.shipmentid getdate(), @asusername from records where records.vend = 'spd' records.shipmentid is not null and not exists ( SELECT tablecheck.ObjectID FROM tablecheck WHERE (Records.shipmentid = tablecheck.ObjectID) AND (tablecheck.Operation = 'Sent to SpeeDee') )
Thanks you for you help..I did that. I have another issues.This my all storeprocedure. By each one by itself it work fine. When I want to run both together I get same error. It is insert all. I need to get information when Objectid is null.I look . I can't see where i made mistake
[Merge][1] [1]:
http://technet.microsoft.com/en-us/library/bb510625.aspx
You could use a temp table to store the information from the first SELECT, then pull one row at a time (TOP 1) from the temp table, insert and then after inserting, delete that row from the temp table. Keep in mind, depending on the amount of data it may not be the best option.
This would certainly work, but it probably would not be effecient. I would only use that technique if there had to be some significant preprocessing done that could not be easily put into a single statement.
Thanks you for you help..I did that. I have another issues.This my all storeprocedure. By each one by itself it work fine. When I want to run both together I get same error. It is insert all. I need to get information when Objectid is null.I look . I can't see where i made mistake @as_username varchar(255) =' ' as begin SET NOCOUNT ON Declare @objectid bigint set @objectid =(SELECT tablecheck.ObjectID FROM tablecheck INNER JOIN Records ON tablecheck.ObjectID = Records.shipmentid WHERE (Records.shipmentid = tablecheck.ObjectID) AND (tablecheck.Operation = 'Sent to SpeeDee')) if @objectid is null begin SELECT Records.[filenumber] as FileVersionNumber, Records.[hawb] as ShipFromShipperNumber, Records.[person] as ShipFromName, Records.[company] as ShipFromAttention, Records.[street_1] ,Records.[street_2] ,Records.[city] ,Records.[state] ,Records.[postal_code] ,Records.[country], ,Records.[phone], Records.second_ship_ref, Records.second_ship_ref, Records.second_ship_ref , Records.second_ship_ref, (CAST(ROUND(Records.[weight],2) as varchar)) as weight , CAST(ROUND(Records.[value],2) as varchar) AS CODValue, ''AS DeclaredValue, '' AS PackageHandling, 'False' as ApplyPackageHandling, CONVERT(varchar(10), Statuses.Report_Date, 101) AS shipDate, Records.hawb AS BillToShipperNumber FROM Records INNER JOIN Statuses ON Records.shipmentid = Statuses.Shipmentid where (Statuses.Status='R' OR Statuses.Status='RC') and Statuses.StatusDescription='Released ' AND Records.[code]='SPD' and NOT EXISTS (SELECT tablecheck.ObjectID FROM tablecheck WHERE (Records.shipment_detail_id = tablecheck.ObjectID) and(tablecheck.Operation = 'Sent to SpeeDee')) END begin INSERT INTO [tablecheck] ([ObjectType] ,[Operation] ,[ObjectID] ,[Date] ,[User]) select distinct 'Consignment', 'Sent to Speedee', records.shipmentid getdate(), @asusername from records where records.vend = 'spd' records.shipmentid is not null and not exists ( SELECT tablecheck.ObjectID FROM tablecheck WHERE (Records.shipmentid = tablecheck.ObjectID) AND (tablecheck.Operation = 'Sent to SpeeDee') ) SET NOCOUNT OFF return 0 end end
16 People are following this question.
