CODEWITHSUNDEEP
sqlsql-server-2008
gruber
gruber

Reputation: 29789

insert into does not work

Why doesn't the query below work:

INSERT INTO [ProcessStatus] ([ProcessId])
SELECT TMP.[ProcessId]

FROM (
SELECT DISTINCT 
     [ProcessId]
FROM [Process]
) TMP

error message is (returns added):

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

Upvotes: 0

Views: 199

Answers (3)

Matt Gibson
Matt Gibson

Reputation: 38238

There doesn't seem to be anything wrong with that. It works for me:

CREATE TABLE #ProcessStatus (ProcessID INTEGER)
CREATE TABLE #Process (ProcessID INTEGER)

INSERT INTO #Process VALUES(1)
INSERT INTO #Process VALUES(2)

INSERT INTO #ProcessStatus (ProcessId)
SELECT TMP.ProcessId
FROM (
SELECT DISTINCT 
     ProcessId
FROM #Process
) TMP

(2 row(s) affected)

Are you absolutely sure about what's going on?

Upvotes: 0

MLT
MLT

Reputation: 514

Assuming [Process] only has the one field (or any others auto increment), you do not need the sub query. Try:

insert into [ProcessStatus] ([ProcessId])
select distinct 
     [ProcessId]
FROM [Process]

EDIT: if you were just wondering why the error message, it should work, are you sure that is where it's failing?

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17997

There may be a buggy trigger on the ProcessStatus table.

Upvotes: 2

Related Questions