Insert statement driving me crazy

[wbodger]

I am trying to do the simplest thing

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

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

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

to work...

 

[gmmastros]

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

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

 

[Olaf Doschke]

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.

 

[wbodger]

OK, I found the trigger

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?

 

[Olaf Doschke]

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

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.