Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

insert and update problem in stored proc Expand / Collapse
Author
Message
Posted Saturday, September 8, 2012 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 10:44 PM
Points: 2, Visits: 11
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
Post #1356364
Posted Monday, September 10, 2012 4:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 4,111, Visits: 5,480
In your delete statement, you have an INNER JOIN to an INSERT statement, where the INSERT does not have an OUTPUT clause.
This is the cause of the given error.

What is it exactly you are attempting to achieve with this procedure?


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1356641
Posted Monday, September 10, 2012 4:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 4,111, Visits: 5,480
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.*



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1356646
Posted Monday, September 10, 2012 5:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.

Post #1356662
Posted Monday, September 10, 2012 5:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 4,111, Visits: 5,480
OTF (9/10/2012)
I don't think the way the OP has written the code is valid Sql in any case.


I'm inclined to agree, which is why i initially asked what the actual requirement is.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1356664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse