subquery returned more than 1 value on an insert into

_{Wednesday, July 9, 2008 6:54 PM}

I have a simple query to insert data from one table to another and I am expecting multiple rows to be inserted but I get an error about subquery returned more than 1 value. When I change the is not null to a specific row, it inserts just fine. What do I need to change to make this run properly. TIA

QUERY

GO

INSERT INTO tbl_client (client_name, client_number)

(SELECT client_name, Client_ID AS Client_Number from CLSQL01.SJDOCS.DOCSADM.CLIENT WHERE Client_ID is not null)

GO

ERROR

Msg 512, Level 16, State 1, Procedure TR_Client_Insert, Line 3

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

_{Wednesday, July 9, 2008 7:10 PM ✅Answered | 4 votes}

You have a TRIGGER on that table which is not coded to take multirow operations into consideration

Denis The SQL Menace

http://www.lessthandot.com/

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx

_{Wednesday, July 9, 2008 7:20 PM ✅Answered}

Try:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[TR_Client_Insert] on [dbo].[tbl_Client]

FOR INSERT,UPDATE

AS

UPDATE [dbo].[tbl_Client]

SET sys_Changed = 1

WHERE EXISTS (

SELECT *

FROM INSERTED AS i

WHERE i.[Client_Number] = [dbo].[tbl_Client].[Client_Number]

)

GO

AMB

_{Wednesday, July 9, 2008 6:58 PM}

Tony,

Try without the parenthesis.

INSERT INTO tbl_client (client_name, client_number)

SELECT client_name, Client_ID

from CLSQL01.SJDOCS.DOCSADM.CLIENT

WHERE Client_ID is not null

GO

AMB

_{Wednesday, July 9, 2008 7:03 PM}

Here is what I have with the same results

GO

INSERT INTO tbl_client (client_name, client_number)

SELECT client_name, Client_ID AS Client_Number from CLSQL01.SJDOCS.DOCSADM.CLIENT WHERE Client_ID is not null

GO

Msg 512, Level 16, State 1, Procedure TR_Client_Insert, Line 3

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

_{Wednesday, July 9, 2008 7:05 PM}

Tony,

Can you tell us if CLSQL01.SJDOCS.DOCSADM.CLIENT is a view?

If so, then post its code.

AMB

_{Wednesday, July 9, 2008 7:12 PM}

It is not a view, straight query of the table, and when I remove the insert it works.

SELECT client_name, Client_ID AS Client_Number from CLSQL01.SJDOCS.DOCSADM.CLIENT WHERE Client_ID is not null

Returns 19002 rows.

And when I do the following

GO

INSERT INTO tbl_client (client_name, client_number)

SELECT client_name, Client_ID AS Client_Number from CLSQL01.SJDOCS.DOCSADM.CLIENT WHERE Client_ID = '101'

GO

It inserts the one row.

_{Wednesday, July 9, 2008 7:13 PM}

Here is the trigger

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [dbo].[TR_Client_Insert] on [dbo].[tbl_Client] FOR INSERT,UPDATE AS

DECLARE @ClientNumber varchar(20)

SET @ClientNumber=(SELECT [Client_Number] from INSERTED)

UPDATE tbl_Client SET sys_Changed=1 WHERE [Client_Number]=@ClientNumber

_{Wednesday, July 9, 2008 7:16 PM}

Good catch Denis!!!

Msg 512, Level 16, State 1, Procedure TR_Client_Insert, Line 3

AMB

_{Wednesday, July 9, 2008 7:35 PM}

That worked, thanks for your help!

_{Thursday, July 15, 2010 5:49 PM}

In my case "You have a TRIGGER on that table which is not coded to take multirow operations into consideration" this worck fine

_{Friday, February 14, 2014 9:49 AM}

It´s right to the spot.... solve my problem.

Thanks.

Best regards.