insert and update problem in stored proc

Question

insert and update problem in stored proc

johndewitte

SSC Journeyman

Points: 76
More actions
September 8, 2012 at 6:46 am

#267374

I want to insert values from a select query into table "Delkk"
The select query
has the following result:
Orderdatum txtKortingBdr Klantnummer Tal
2010-09-07 13:55:55.000 14,1305 1445 5
2010-09-07 16:58:41.000 5,695 2128 1
Delkk has the same columns:
Orderdatum, Tal, txtKortingBdr,Klantnummer.
When I try to execute the stored proc "DelKK2jaar"
I get the following message in SSMS:
"A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause."
The stored proc :
ALTER PROCEDURE [dbo].[DelKK2jaar]
AS
BEGIN
SET NOCOUNT ON;
DELETE
A
FROM
klantenkaart AS A
INNER JOIN
(
INSERT
Delkk
(
Orderdatum,
txtKortingBdr,
Klantnummer,
Tal
)
OUTPUT Inserted.* into delkk
( Orderdatum, txtKortingBdr,Klantnummer, Tal)
select
Max(klantenkaart.Orderdatum) AS Orderdatum,
txtKortingBdr,
Klant.Klantnummer,
Count(Klant.txtKortingBdr) AS Tal
FROM
Gemeente
INNER JOIN
Klant
ON Gemeente.GemeenteId = Klant.GemeenteId
INNER JOIN
klantenkaart
ON Klant.Klantnummer = klantenkaart.Klantnummer
GROUP BY
Klant.Klantnummer,
Klant.Naamvoornaam,
Klant.txtKortingBdr,
Gemeente.Gemeente
HAVING
Max(klantenkaart.Orderdatum) < DATEADD(YEAR, -2, SYSDATETIME())
) AS B
ON A.klantnummer = B.Klantnummer
END
Can somebody have a look and help me?
Txs,
John
September 10, 2012 at 4:38 am

This was removed by the editor as SPAM
September 10, 2012 at 4:44 am

This was removed by the editor as SPAM
September 10, 2012 at 5:29 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

OTF SSCertifiable Points: 6054 More actions · Answer 1

Stewart "Arturius" Campbell (9/10/2012)
Alternatively, in the OUTPUT Inserted.* into delkk
( Orderdatum, txtKortingBdr,Klantnummer, Tal) line, remove the " into delkk" statement, i.e. the OUTPUT statement should reflect OUTPUT Inserted.*

That's what I thought initially, however, if you try to run the code with the modification suggested it

presents the following error:

Msg 10727, Level 15, State 1, Procedure DelKK2jaar, Line 17

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed on either side of a JOIN or APPLY operator

I don't think the way the OP has written the code is valid Sql in any case.

I think in order to do what he wants to do he would have to:

Run the nested insert with the output clause into a #temp table or variable

Do the Join on that to perform any other actions.