Solved

SQL problem with insert

Posted on 2014-03-11
4
321 Views
Last Modified: 2014-03-21
I am trying to use the sql command below to duplicate subcategories from product list 1 to product list 2. The select statement returns the expected results but when I try to use it as a subquery it fails. Can anyone tell me how I can make this work?

INSERT INTO slprodsubcat
     (prodlist, catcode, subcatcode, subcatdesc)
  SELECT '2' as prodlist, catcode, subcatcode, subcatdesc
    FROM slprodsubcat
	WHERE prodlist = '1'

Open in new window

0
Comment
Question by:J C
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39922272
What happens when it fails? And do you have the code for you subquery?
0
 

Author Comment

by:J C
ID: 39922279
I included the entire command I am running. I am referring to the select statement as the subquery.

Here is the error:

PSC-init
Msg 512, Level 16, State 1, Procedure _TI_slprodsubcat, Line 1
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.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 250 total points
ID: 39922284
A select for an insert is not a sub-query. A sub-query is a select within a select.

It would appear from that error message that a trigger is running when you insert data into the table, (maybe called _TI_slprodsubcat otherwise that is an SP being run). And within that trigger a sub-query is returning multiple results as the error says.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 39922477
If there is a TRIGGER, my guess would be someone wrote it thinking that it fires once per row, rather than once per statement, hence the error.  In other words, the TRIGGER has to be re-written correctly to support multiple INSERTs.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

707 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