nested INSERT statement an output clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

nested INSERT statement an output clause

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2012-09-08 : 01:02:00

I want to insert values from a select query into tabel "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 :
[code]
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
[\code]

Can somebody have a look and help me?

Txs,

John

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-08 : 06:57:33

The part of the code shown below has incorrect syntax and incorrect structure. Can you explain in words what you are trying to accomplish?

DELETE 
A
FROM
klantenkaart AS A
INNER JOIN
(
INSERT
Delkk
(
Orderdatum, 
txtKortingBdr, 
Klantnummer, 
Tal
)
OUTPUT Inserted.* into delkk
( Orderdatum, txtKortingBdr,Klantnummer, Tal)

JohnDW
Starting Member

45 Posts

Posted - 2012-09-08 : 09:36:21

I select data from table 'klantenkaart' (columns: orderdatum, txtKortingBdr, Klantnummer, Tal) whose last 'orderdatum' (=orderdate) per 'klantnummer'(=customer number) is more than two years ago.
I select the Orderdates (column 'orderdatum') from each client (column 'klantnummer') who has bought products that where inserted in table 'klantenkaart' where the orderdate ('orderdatum') is more then 2years ago. (in the loyalty card)
Queryy1:
[code]
select
Max (klantenkaart.Orderdatum) AS OrderDatum,
txtKortingBdr,
Klant.Klantnummer,
Count (Klant.txtKortingBdr) AS Tal

FROM
klantenkaart
INNER JOIN
klantnummer
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 ())
[/ code]

This query has results, those results must come in the table 'DelKK'.
[code]
INSERT
Delkk
(
OrderDatum,
txtKortingBdr,
klantnummer,
Tal
)
---this code is wrong, here I have a problem-------------------
OUTPUT Inserted. * into delkk
(OrderDatum, txtKortingBdr, klantnummer, Tal)
[/ code]

Afterwards, data must be removed from 'klantenkaart' (=customer card)
whose klantnummer exists in thequery (=Query1).

[code]
DELETE
A
FROM
klantenkaart AS A
[/ code]

This all should happens in one stored procedure.

The problem is the insert into table 'delkk'

I hope my explanation is clear.

We appreciate your help.

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 12:55:42

[code]
INSERT
Delkk
(
OrderDatum,
txtKortingBdr,
klantnummer,
Tal
)
select
Max (klantenkaart.Orderdatum) AS OrderDatum,
txtKortingBdr,
Klant.Klantnummer,
Count (Klant.txtKortingBdr) AS Tal

FROM
klantenkaart
INNER JOIN
klantnummer
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 ())

DELETE A
FROM klantenkaart AS A
INNER JOIN Delkk AS D
ON D.klantnummer = A.klantnummer
AND D.txtKortingBdr = A.txtKortingBdr
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources