Subquery returned more than 1 value. This is not permitted

ALTER TRIGGER t1
ON dbo.Customers

FOR INSERT
AS

BEGIN TRANSACTION

/* variables */

DECLARE
    @maxid bigint

SELECT @customerid = id FROM inserted

SET IDENTITY_INSERT dbo.new_table ON

DECLARE
    @maxid bigint

SELECT @maxid = MAX(ID) FROM new_table

INSERT INTO new_table (ID, ParentID, Foo, Bar, Buzz)
    SELECT ID+@maxid, ParentID+@maxid, Foo, Bar, Buzz FROM initial_table

SET IDENTITY_INSERT dbo.new_tableOFF

/* execute */
COMMIT TRANSACTION

GO

fails with:

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

How to fix it?

What I am trying to do is

insert id and parentid, each INCREASED by @maxid
from initial_table
into new_table

thnx

new_table

id (bigint) 
parentid (bigint - linked to id) 
foo | bar | buzz (others are nvarchar, not really important)

initial table

id (bigint) 
parentid (bigint - linked to id)
foo | bar | buzz (others are nvarchar, not really important)

Solution

You are battling against a few errors I suspect.

1. You are inserting values that violate a unique constraint in new_table. Avoid the existence error by joining against the table you are inserting into. Adjust the join condition to match your table's constraint:

insert into new_table (ID, ParentID, Foo, Bar, Buzz)
    select  ID+@maxid, ParentID+@maxid, Foo, Bar, Buzz 
    from    initial_table i
    left
    join    new_table N on 
            i.ID+@maxid = n.ID or 
            i.ParentID+@maxid = n.ParentId
    where   n.ID is null --make sure its not already there

2. Somewhere, a subquery has returned multiple rows where you expect one. The subquery error is either in the code that inserts into dbo.Customer (triggering t1), or perhaps in a trigger defined on new_table. I do not see anything in the posted code that would throw the subquery exception.

Triggers (aka, landmines) inserting into tables that have triggers defined on them is a recipe for pain. If possible, try to refactor some of this logic out of triggers and into code you can logically follow.