April 26, 2011 at 1:46 pm
What I started with was a procedure that does an INSERT, then assigns @@IDENTITY to a variable (dangerous!) and does another INSERT using the IDENTITY (ID) from the first insert. In order to both prevent the inevitable wrong value from being inserted on the second insert, I wanted to put the second insert into the OUTPUT clause of the first insert. The problem being the second insert uses a subquery to extrapolate the data for the insert. After much Googling, I could not find an example anywhere close to what I was trying to do, so I thought I would ask you gurus if what I am trying to do is even possible.
I have included everything below: (There are comments in the OUTPUT section in question.)
CREATE TABLE [FP_AL](
[ID] [int] IDENTITY(1,1) NOT NULL,
[bID] [int] NULL,
[cID] [int] NULL,
[fileSource] [varchar](20) NULL,
[impDate] [smalldatetime] NULL,
[recCount] [int] NULL,
[totRev] [decimal](18,2) NULL,
[dateRun] [smalldatetime] NULL,
[comment] [varchar](300) NULL
)
GO
CREATE TABLE [APA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FP_AL_ID] [int] NULL,
[bID] [int] NULL,
[cID] [int] NULL,
[aID] [int] NULL,
[PID] [int] NULL,
[azID] [int] NULL,
[pzID] [int] NULL,
[ptID] [int] NULL,
[atID] [int] NULL,
[pzName] [varchar](50) NULL,
[AccountNumber] [varchar](50) NULL,
[azNumber] [varchar](50) NULL,
[fNumber] [varchar](10) NULL,
[category] [varchar](50) NULL,
[qty] [decimal](18, 6) NULL,
[price] [decimal](18, 6) NULL,
[total] [decimal](18, 2) NULL,
[impDate] [smalldatetime] NULL,
[prevImpDate] [smalldatetime] NULL,
[prevImpAmount] [decimal](18, 2) NULL,
[transAmt] [decimal](18, 2) NULL,
[updateDate] [smalldatetime] NULL)
GO
CREATE TABLE [PEP](
[ID] [int] IDENTITY(1,1) NOT NULL,
[bID] [int] NULL,
[AccountNumber] [varchar](50) NULL,
[Description] [varchar(50) NULL,
[IMPDate] [smalldatetime] NULL,
[TransQty] [decimal](18, 4) NULL)
GO
INSERT PEP
(bID,AccountNumber,ImpDate,TransQty)
SELECT 1,'00000000001111111111','DESC1','2011/04/20',400.0145
UNION ALL
SELECT 1,'00000000001111111112','DESC2','2011/04/20',1900.2000
UNION ALL
SELECT 1,'00000000001111111113','DESC3','2011/04/29',50.0202
UNION ALL
SELECT 1,'00000000001111111114','DESC4','2011/04/29',40.0357
CREATE TABLE [PMP](
[ID] [int] IDENTITY(1,1) NOT NULL,
[bID] [int] NULL,
[AccountNumber] [varchar](50) NULL,
[Description] [varchar(50) NULL,
[VDate] [smalldatetime] NULL,
[PQty] [decimal](18, 4) NULL)
GO
INSERT PMP
(bID,AccountNumber,VDate,PQty)
SELECT 1,'00000000001111111121','DESC5','2011/04/29',500.0111
UNION ALL
SELECT 1,'00000000001111111122','DESC6','2011/04/29',1800.2333
UNION ALL
SELECT 1,'00000000001111111123','DESC7','2011/04/20',500.1232
UNION ALL
SELECT 1,'00000000001111111124','DESC8','2011/04/20',454.0974
CREATE TABLE [LDOM](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[dateComplete] [smalldatetime] NOT NULL)
GO
INSERT LDOM (dateComplete)
SELECT '2011-01-31 00:00:00'
UNION ALL
SELECT '2011-02-28 00:00:00'
UNION ALL
SELECT '2011-03-31 00:00:00'
UNION ALL
SELECT '2011-04-29 00:00:00'
UNION ALL
SELECT '2011-05-31 00:00:00'
UNION ALL
SELECT '2011-06-30 00:00:00'
UNION ALL
SELECT '2011-07-29 00:00:00'
UNION ALL
SELECT '2011-08-31 00:00:00'
UNION ALL
SELECT '2011-09-30 00:00:00'
UNION ALL
SELECT '2011-10-31 00:00:00'
UNION ALL
SELECT '2011-11-30 00:00:00'
UNION ALL
SELECT '2011-12-30 00:00:00'
CREATE TABLE [Accounts](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[bID] [int] NULL,
[cID] [int] NULL,
[pID] [int] NULL,
[aTypeID] [int] NULL,
[AccountNumber] [varchar](20) NULL)
GO
INSERT Accounts(bID,cID,pID,aTypeID,AccountNumber)
SELECT 1,111,99,1,'00000000001111111111'
UNION ALL
SELECT 1,111,99,1,'00000000001111111112'
UNION ALL
SELECT 1,111,99,1,'00000000001111111113'
UNION ALL
SELECT 1,111,99,1,'00000000001111111114'
UNION ALL
SELECT 1,111,99,2,'00000000001111111121'
UNION ALL
SELECT 1,111,99,2,'00000000001111111122'
UNION ALL
SELECT 1,111,99,2,'00000000001111111123'
UNION ALL
SELECT 1,111,99,2,'00000000001111111124'
-- QUERY
DECLARE @CurrentVDate smalldatetime
DECLARE @bID int
SET @bID = 1
DECLARE @CurDate DateTime
SET @CurDate = GetDate()
SET @CurrentVDate = (SELECT MAX(IMPDate) FROM PEP WHERE bID = @bID)
IF NOT @CurrentVDate IN (SELECT impDate FROM FP_AL WHERE bID = @bID AND cID = 111)
BEGIN
BEGIN TRANSACTION
INSERT FP_AL
(bID, cID, fileSource, impDate, recordCount, totalX, dateRun, comment)
-----------
OUTPUT
-- I know how to use this part, what I need to know is...
INSERTED.ID As FP_AL_ID
-- ... Is THIS possible? I couldn't find any examples if it is...
(SELECT
A.bID, A.cID, A.ID, A.pID, PEP.ID, 0, 8, A.aTypeID, PEP.Description,
PEP.AccountNumber, PEP.AccountNumber,NULL,'B-Account', PEP.TransQty,
@CurDate, PEP.impDate, NULL, NULL, NULL
FROM PEP
JOIN Accounts A ON PEP.AccountNumber = A.AccountNumber
JOIN LDOM on impDate = dateComplete
WHERE (PEP.TransQty > 0) AND (PEP.bID = @bID))
UNION
INSERTED.ID As FP_AL_ID
(SELECT
A.bID, A.cID, A.ID, A.pID, PMP.ID, 0, 8, A.aTypeID, PMP.Description,
PMP.AccountNumber, PMP.AccountNumber,NULL,'B-Account', PMP.PQty,
@CurDate, PMP.VDate, NULL, NULL, NULL
FROM PMP
JOIN Accounts A ON PMP.AccountNumber = A.AccountNumber
JOIN LDOM on VDate = dateComplete
WHERE (PMP.PQty > 0) AND (PMP.bID = @bID)
)
INTO APA
FP_AL_ID, bID, cID, aID, PID, azID, pzID, ptID, atID, pzName, AccountNumber,
azNumber, fNumber, category, qty, impDate, prevImpDate, prevImpAmount, transAmt,
updateDate)
-----------
SELECT bID, 111, 'SUNGO', impDate, SUM(recordCount), SUM(totalINV), @CurDate, ''
FROM
(
SELECT bID, IMPDate, count(*) As recordCount, SUM(TransQty) As totalINV
FROM PEP
INNER JOIN LDOM on IMPDate = dateComplete
WHERE bID = @bID
AND IMPDate = @CurrentVDate
GROUP BY IMPDate, bID
UNION
SELECT bID, VDate, count(*) As recordCount, SUM(PQty) As totalINV
FROM PMP
INNER JOIN LDOM on VDate = dateComplete
WHERE bID = @bID
AND VDate = @CurrentVDate
GROUP BY VDate, bID
)
GROUP BY impDate, bID
COMMIT TRANSACTION
END
April 26, 2011 at 2:04 pm
Output doesn't really support full "select" syntax. But it's easy to get around that. Output into a temp table or table variable, then use that for your second insert, since you can join, etc., with one of those. Adds one step to the query, but I've not found that to be a serious issue in my implementations of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2011 at 2:10 pm
Below is the original procedure prior to my attempt at modifying it to use the OUTPUT clause.
P.S.: There is an error in the above code (beyond the obvious) the WHERE in the bottom half of the UNION on the subquery should ONLY read "WHERE PEP.bID = @bID" instead of having the "(PMP.PQty > 0) AND". Shouldn't matter, but to compare oranges to oranges...
DECLARE @CurrentVDate smalldatetime
DECLARE @bID int
SET @bID = 1
DECLARE @CurDate DateTime
SET @CurDate = GetDate()
DECLARE @FP_AL_ID int
SET @CurrentVDate = (SELECT MAX(IMPDate) FROM PEP WHERE bID = @bID)
IF NOT @CurrentVDate IN (SELECT impDate FROM FP_AL WHERE bID = @bID AND cID = 111)
BEGIN
BEGIN TRANSACTION
INSERT FP_AL
(bID, cID, fileSource, impDate, recordCount, totalX, dateRun, comment)
SELECT bID, 111, 'SUNGO', impDate, SUM(recordCount), SUM(totalINV), @CurDate, ''
FROM
(
SELECT bID, IMPDate, count(*) As recordCount, SUM(TransQty) As totalINV
FROM PEP
INNER JOIN LDOM on IMPDate = dateComplete
WHERE bID = @bID
AND IMPDate = @CurrentVDate
GROUP BY IMPDate, bID
UNION
SELECT bID, VDate, count(*) As recordCount, SUM(PQty) As totalINV
FROM PMP
INNER JOIN LDOM on VDate = dateComplete
WHERE bID = @bID
AND VDate = @CurrentVDate
GROUP BY VDate, bID
) SUB1
GROUP BY impDate, bID
SET @FP_AL_ID = @@IDENTITY
INSERT APA
(FP_AL_ID, bID, cID, aID, PID, azID, pzID, ptID, atID, pzName, AccountNumber,
azNumber, fNumber, category, qty, impDate, prevImpDate, prevImpAmount, transAmt,
updateDate)
SELECT @FP_AL_ID, A.bID, A.cID, A.ID, A.pID, PEP.ID, 0, 8, A.aTypeID, PEP.Description,
PEP.AccountNumber, PEP.AccountNumber,NULL,'B-Account', PEP.TransQty,
@CurDate, PEP.impDate, NULL, NULL, NULL
FROM PEP
JOIN Accounts A ON PEP.AccountNumber = A.AccountNumber
JOIN LDOM on impDate = dateComplete
WHERE (PEP.TransQty > 0) AND (PEP.bID = @bID)
UNION
SELECT @FP_AL_ID, A.bID, A.cID, A.ID, A.pID, PMP.ID, 0, 8, A.aTypeID, PMP.Description,
PMP.AccountNumber, PMP.AccountNumber,NULL,'B-Account', PMP.PQty,
@CurDate, PMP.VDate, NULL, NULL, NULL
FROM PMP
JOIN Accounts A ON PMP.AccountNumber = A.AccountNumber
JOIN LDOM on VDate = dateComplete
WHERE PMP.bID = @bID
COMMIT TRANSACTION
END
April 26, 2011 at 2:12 pm
I like the table variable idea! Thanks G2
KK
April 27, 2011 at 7:29 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2011 at 7:52 am
For the benefit of future generations, here is what my final product using the table variable solution looks like:
DECLARE @CurrentVDate smalldatetime
DECLARE @bID int
SET @bID = 1
DECLARE @CurDate DateTime
SET @CurDate = GetDate()
--Declare the table variable
DECLARE @FPALID table(FP_AL_ID int, bID int)
SET @CurrentVDate = (SELECT MAX(IMPDate) FROM PEP WHERE bID = @bID)
IF NOT @CurrentVDate IN (SELECT impDate FROM FP_AL WHERE bID = @bID AND cID = 111)
BEGIN
BEGIN TRANSACTION
INSERT FP_AL
(bID, cID, fileSource, impDate, recordCount, totalX, dateRun, comment)
-----------
OUTPUT -- Insert output values to table variable
INSERTED.ID As FP_AL_ID,
INSERTED.bID
INTO @FPALID
(FP_AL_ID, bID)
-----------
SELECT bID, 111, 'SUNGO', impDate, SUM(recordCount), SUM(totalINV), @CurDate, ''
FROM
(
SELECT bID, IMPDate, count(*) As recordCount, SUM(TransQty) As totalINV
FROM PEP
INNER JOIN LDOM on IMPDate = dateComplete
WHERE bID = @bID
AND IMPDate = @CurrentVDate
GROUP BY IMPDate, bID
UNION
SELECT bID, VDate, count(*) As recordCount, SUM(PQty) As totalINV
FROM PMP
INNER JOIN LDOM on VDate = dateComplete
WHERE bID = @bID
AND VDate = @CurrentVDate
GROUP BY VDate, bID
) SUB1
GROUP BY impDate, bID
INSERT APA
(FP_AL_ID, bID, cID, aID, PID, azID, pzID, ptID, atID, pzName, AccountNumber,
azNumber, fNumber, category, qty, impDate, prevImpDate, prevImpAmount, transAmt,
updateDate)
SELECT FP.FP_AL_ID, -- Use the column from the join
A.bID, A.cID, A.ID, A.pID, PEP.ID, 0, 8, A.aTypeID, PEP.Description,
PEP.AccountNumber, PEP.AccountNumber,NULL,'B-Account', PEP.TransQty,
@CurDate, PEP.impDate, NULL, NULL, NULL
FROM PEP
JOIN Accounts A ON PEP.AccountNumber = A.AccountNumber
JOIN LDOM on impDate = dateComplete
JOIN @FPALID FP ON A.bID = FP.bID -- JOIN back to the table variable
WHERE (PEP.TransQty > 0) AND (PEP.bID = @bID)
UNION
SELECT FP.FP_AL_ID, -- Use the column from the join
A.bID, A.cID, A.ID, A.pID, PMP.ID, 0, 8, A.aTypeID, PMP.Description,
PMP.AccountNumber, PMP.AccountNumber,NULL,'B-Account', PMP.PQty,
@CurDate, PMP.VDate, NULL, NULL, NULL
FROM PMP
JOIN Accounts A ON PMP.AccountNumber = A.AccountNumber
JOIN LDOM on VDate = dateComplete
JOIN @FPALID FP ON A.bID = FP.bID -- JOIN back to the table variable
WHERE PMP.bID = @bID
COMMIT TRANSACTION
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply