Solved

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

Posted on 2013-05-10
2
373 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
0
Comment
Question by:splanton
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39156803
INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId)
SELECT DISTINCT Contractor.ContractorId, 67
      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)
            )
0
 
LVL 2

Author Closing Comment

by:splanton
ID: 39156948
Ah! I see... :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question