Solved

SQL problem with insert

Posted on 2014-03-11
4
318 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

740 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