We help IT Professionals succeed at work.
Get Started

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

397 Views
Last Modified: 2013-05-10
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
Comment
Watch Question
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE