Solved

SQL problem with insert

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now