Avatar of splanton
splanton
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Using a SELECT with Subquery as a source for an INSERT.

I have a rather convoluted query to drill down through several tables and return a distinct list  of values (in this case ontractor Id's)

The query is as follows:

SELECT DISTINCT Contractor.ContractorId
FROM	Contractor 
	INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
WHERE	ContractorContainer.ContainerId IN
	(SELECT Container.ContainerID
	 FROM	Container 
		INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId 
		INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId 
		INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId 
		INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
	WHERE     (WasteSubType.WasteSubTypeID = 67)
	) 
ORDER BY Contractor.ContractorID

Open in new window


It produces a lovely unique list of ContractId's

ContractorId
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...

Open in new window


I now need to do an insert based upon these results that I thought might look something like this:

INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId) 
VALUES (
	(SELECT DISTINCT Contractor.ContractorId
	FROM	Contractor 
		INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
	WHERE	ContractorContainer.ContainerId IN
		(SELECT Container.ContainerID
		 FROM	Container 
			INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId 
			INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId 
			INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId 
			INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
		WHERE     (WasteSubType.WasteSubTypeID = 67)
		) 
	),
	67)

Open in new window


Theat just results in a "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." message.

What am I doing wrong here?

Regards
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
splanton

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Aneesh

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
splanton

ASKER
Ah! I see... :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck