INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert statement driving me crazy

Insert statement driving me crazy

(OP)
I am trying to do the simplest thing

CODE

insert into dbo.tbl_Slipper(Job,JOB_DELIVERY_DATE,QTY_TO_MAKE)
select Job,JOB_DELIVERY_DATE,QTY_TO_MAKE
from dbo.tbl_Slipper_Test 

Insert values from one table into another, but I keep getting this

CODE

Msg 512, Level 16, State 1, Procedure ForInsert, Line 10 [Batch Start Line 0]
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. 

Any idea what I am or might be missing? The query runs just fine on it's own and returns 1717 records, each of which I want to insert into the other table.

I can't even get

CODE

insert into dbo.tbl_Slipper(Job)
select '00000000'
from dbo.tbl_Slipper_Test 

to work...

RE: Insert statement driving me crazy

I think you have a trigger on the table that isn't working properly. To see the triggers on the table...

CODE

sp_helptrigger 'tbl_slipper' 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Insert statement driving me crazy

Yes, take a close look at the error message, it's not about the insert statement but about Line 10 of the "Procedure ForInsert".

Most probably the trigger is programmed for the case of a single new row. Even your last try has '00000000' repeated for each record of dbo.tbl_Slipper_Test
Reprogram the trigger to be able to process the inserted table as a table and not as a single row.

Bye, Olaf.

RE: Insert statement driving me crazy

(OP)
OK, I found the trigger

CODE

CREATE TRIGGER [dbo].[ForInsert] ON [dbo].[tbl_Slipper] 
FOR INSERT
AS

declare @Job as nvarchar(50)
declare @QTM as integer
declare @JDD as datetime
declare @Accepted as datetime
declare @UserName as nvarchar(50)
set @Job = (Select Job from inserted)
set @QTM = (Select QTY_TO_MAKE from inserted)
set @JDD = (Select JOB_DELIVERY_DATE from inserted)
set @Accepted = (Select Accepted from inserted)
set @UserName = (Select UserName from inserted)


insert into tbl_SlipperHistory (Job, QTY_TO_MAKE, JOB_DELIVERY_DATE, Accepted, UserName)
values (@Job,@QTM,@JDD,@Accepted,@UserName)
GO

ALTER TABLE [dbo].[tbl_Slipper] ENABLE TRIGGER [ForInsert] 

So. is it that it only wants to insert one record at a time into the history table?

RE: Insert statement driving me crazy

Yes, this is programmed for single rows only.

All the SETs don't work for the case of more than one row in the "inserted" table.

You'd just need

CODE

CREATE TRIGGER [dbo].[ForInsert] ON [dbo].[tbl_Slipper] 
FOR INSERT
AS
insert into tbl_SlipperHistory (Job, QTY_TO_MAKE, JOB_DELIVERY_DATE, Accepted, UserName)
select Job, QTY_TO_MAKE, JOB_DELIVERY_DATE, Accepted, UserName from inserted 

Even if there would only be one row in inserted, who on earth has the idea it should first be copied into single variables to make a single row insert?

The ALTER TABLE also shouldn't be part of the trigger. If the trigger runs, it surely already is enabled.
If at all, you'd do this once outside of the trigger, eg via SSMS query.

Bye, Olaf.

Edit: Since the ALTER TABLE is after a GO that isn't part of the trigger anyway. So forget about that criticism. The only thing unneccessary about it is, a CREATE TRIGGER enables the new trigger by default.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close