June 13, 2008 at 10:15 am
OK, that topic doesn't make much sense, but I'm not sure how else to phrase it. Well, then, how about some code?
CREATE TABLE Entry(
id INT IDENTITY PRIMARY KEY,
accountId INT NOT NULL,
transactionDate SMALLDATETIME NOT NULL,
sequenceNumber TINYINT NOT NULL DEFAULT ((0)),
primaryTransaction INT NULL,
primaryTransactionSequence TINYINT NULL,
cleared TINYTIN NOT NULL DEFAULT ((0)),
payeeId INT NOT NULL,
entryType VARCHAR(8) NULL,
note VARCHAR(250) NULL,
FOREIGN KEY (primaryTransaction, primaryTransactionSequence)
REFERENCES EntryItem(entryId, sequence))
CREATE TABLE Category(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(40) NOT NULL,
accountId INT NULL)
CREATE TABLE EntryItem(
entryId INT NOT NULL REFERENCES Entry(id) ON DELETE CASCADE,
sequence TINYINT NOT NULL,
amount NUMERIC(18, 2) NOT NULL,
categoryId INT NOT NULL REFERENCES Category(id),
note VARCHAR(100) NULL,
PRIMARY KEY (entryId, sequence))
CREATE TABLE Schedule(
entryId INT PRIMARY KEY,
nextDate SMALLDATETIME NOT NULL)
An entry is a financial transaction (wanted to use transaction as the table name, obviously not a good idea). It has one or more EntryItems (zero is not a valid state, but SQL can't enforce that). Further, an Entry may be primary or secondary (transfer). A secondary transaction has non-null PrimaryTransaction and PrimaryTransactionSequence values.
I'm trying to copy a primary Entry, its EntryItems, its secondary Entries and their respective secondary EntryItems all into new records in the same tables. There is only one primary entry to be copied. (The copy is the recording of one instance of a scheduled recurring entry, while leaving the scheduled entry as-is.)
Copying the primary entry and its items is straightforward:
CREATE PROCEDURE RecordNextScheduledEntry (@entryId INT)
AS
DECLARE @newEntryId INT
-- create entry primary entry
INSERT INTO Entry (accountId, transactionDate, sequenceNumber, cleared, payeeId, entryType, note)
SELECT SE.accountId, S.nextDate, 0, 0, SE.payeeId, SE.entryType, SE.note
FROM Entry SE JOIN Schedule S ON SE.id = S.entryId
WHERE SE.id = @entryId
SELECT @newEntryId = SCOPE_IDENTITY()
-- create primary entry items
INSERT INTO EntryItem (entryId, sequence, amount, categoryId, note)
SELECT @newEntryId, I.sequence, I.amount, I.categoryId, I.note
FROM EntryItem I
WHERE I.entryId = @entryId
However, that process relies on the SCOPE_IDENTITY function, which returns only the last identity. Now, what about inserting copies of the secondary entries? Here's what I have now (which is what I want to do, but contains a syntax error):
DECLARE @transferTable TABLE (actualId INT, scheduleId INT)
INSERT INTO Entry (accountId, transactionDate, sequenceNumber, primaryTransaction, primaryTransactionSequence, cleared, payeeId, entryType, note)
OUTPUT INSERTED.id, T.id INTO @transferTable
SELECT T.accountId, S.nextDate, 0, @newEntryId, I.sequence, 0, T.payeeId, T.entryType, T.note
FROM Entry T JOIN EntryItem I ON T.id = I.entryId
JOIN Schedule S ON S.entryId = T.primaryTransaction
JOIN Category C ON I.categoryId = C.id
WHERE T.primaryTransaction = @entryId
AND C.accountId = T.accountId
This would work, without the OUTPUT clause, which refuses to see "T.id" because it's not part of INSERTED. But lacking that, how does the next query work?
INSERT INTO EntryItem (entryId, sequence, amount, categoryId, note)
SELECT T.actualId, I.sequence, I.amount, I.categoryId, I.note
FROM @transferTable T
JOIN EntryItem I ON I.entryId = T.scheduleId
How do I populate the table variable @transferTable if the OUTPUT clause won't do it?
June 13, 2008 at 10:36 am
This might not apply in a lot of cases, but would it make sense to have a "throwaway" column that would allow you to get the "old" ID involved? Depending on how hard it is to correlate back to the previous record, it might be worth just adding a new column (say - ancestorID or PreviousID). At that point the OUTPUT holds what you want, and it does simply things some.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 10:40 am
I thought about that, but it just doesn't seem right. This is an isolated case, most Entry rows won't be inserted as scheduled transfers. Entry will be the largest table in the database and it seems wrong to put in that column when it will serve no other use.
That would work, though, yet I wonder if a cursor solution is better. The extra column will take up space in the largest table, while a cursor would be limited to this procedure, which in most cases will yield 0 or 1 transfer entries (any more than 10 is unthinkable, although there is no actual limit).
June 13, 2008 at 10:54 am
Are the other columns involved in the insert enough to be used as a natural key? I understand that would be a multi-column join, and probably not the most performant thing, but still - ought to do better than a cursor.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 11:25 am
Ok, I made my own schema to simplify the example a bit.
Rather than dealing with attempting to keep the ID from the original table, I ordered the INSERT.
Then, because I have a fixed order, I was able to use ROW_NUMBER to really generate a surrogate key for me based on the order of the identity column.
I could then use this surrogate key to join back to my original group of records based on their ROW_NUMBER using the same ORDER BY.
[font="Courier New"]DROP TABLE #tmpT1
GO
DROP TABLE #tmpT2
GO
DROP TABLE #tmpT3
GO
CREATE TABLE #tmpT1 (T1ID INT NOT NULL IDENTITY(1,1), Descr VARCHAR(10))
CREATE TABLE #tmpT2 (T2ID INT NOT NULL IDENTITY(1,1), T1ID INT, Descr VARCHAR(10))
CREATE TABLE #tmpT3 (T3ID INT NOT NULL IDENTITY(1,1), T2ID INT, Descr VARCHAR(10))
DECLARE @ID INT, @ID2 INT
INSERT #tmpT1 (Descr) VALUES ('GP1')
SELECT @ID = SCOPE_IDENTITY()
INSERT #tmpT2 (T1ID, Descr) VALUES (@ID, 'P1')
SELECT @ID2 = SCOPE_IDENTITY()
INSERT #tmpT3 (T2ID,Descr) VALUES (@ID2,'C1')
INSERT #tmpT3 (T2ID,Descr) VALUES (@ID2,'C2')
INSERT #tmpT2 (T1ID, Descr) VALUES (@ID, 'P2')
SELECT @ID2 = SCOPE_IDENTITY()
INSERT #tmpT3 (T2ID,Descr) VALUES (@ID2,'C3')
INSERT #tmpT3 (T2ID,Descr) VALUES (@ID2,'C4')
SELECT * FROM #tmpT1
SELECT * FROM #tmpT2
SELECT * FROM #tmpT3
GO
/* Make Copy */
DECLARE @CopyID INT
, @NewID INT
SET @CopyID = 1
--Copy Parent
INSERT #tmpT1 (Descr) SELECT 'C-' + Descr FROM #tmpT1 WHERE T1ID = @CopyID
--Get identity
SELECT @NewID = SCOPE_IDENTITY()
INSERT #tmpT2 (T1ID, Descr)
SELECT @NewID, 'C-' + Descr FROM #tmpT2 WHERE T1ID = @CopyID ORDER BY T2ID --This is the key to this working
/* since the new records and the original records have their identity values in the same order, I can create a surrogate key based on that order by using ROW_NUMBER */
; WITH CurRows (T2ID, T1ID, Descr, RowNum)
AS (SELECT T2ID, T1ID, Descr, ROW_NUMBER() OVER (ORDER BY T2ID)
FROM #tmpT2 WHERE T1ID = @NewID)
, PrevRows (T2ID, T1ID, Descr, RowNum)
AS (SELECT T2ID, T1ID, Descr, ROW_NUMBER() OVER (ORDER BY T2ID)
FROM #tmpT2 WHERE T1ID = @CopyID)
INSERT #tmpT3 (T2ID,Descr)
SELECT
T.T2ID
, 'C-' + S.Descr
FROM
CurRows T
INNER JOIN PrevRows P ON T.RowNum = P.RowNum
INNER JOIN #tmpT3 S ON S.T2ID = P.T2ID
SELECT * FROM #tmpT1
SELECT * FROM #tmpT2
SELECT * FROM #tmpT3[/font]
June 13, 2008 at 11:40 am
very nice idea Michael. Order by does guarantee that the identities will be assigned in that order, so that's a very nice catch.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 1:00 pm
You've simplified the schema... I see that... I have no idea what those tables are! I'm having trouble figuring out how it matches my problem. How do T1, T2 and T3 map to the schema that I posted?
June 13, 2008 at 1:07 pm
Wait, I think I got it... wrote something that compiles, now I have to test it...
June 13, 2008 at 1:15 pm
Sorry, I didn't want to code inserts with all of the additional columns.
The important parts are where I put comments - order the insert by the original identity column and use ROW_NUMBER to join between the old records and the new ones.
June 13, 2008 at 1:38 pm
It works! I've posted the completed procedure below (goes with the schema from the first post).
CREATE PROCEDURE RecordNextScheduledEntry (@entryId INT)
AS
DECLARE @newEntryId INT
-- create primary entry
INSERT INTO Entry (accountId, transactionDate, sequenceNumber, cleared, payeeId, entryType, note)
SELECT SE.accountId, S.nextDate, 0, 0, SE.payeeId, SE.entryType, SE.note
FROM Entry SE JOIN Schedule S ON SE.id = S.entryId
WHERE SE.id = @entryId
SELECT @newEntryId = SCOPE_IDENTITY()
-- create primary entry items
INSERT INTO EntryItem (entryId, sequence, amount, categoryId, note)
SELECT @newEntryId, I.sequence, I.amount, I.categoryId, I.note
FROM EntryItem I
WHERE I.entryId = @entryId
-- create transfer entries
INSERT INTO Entry (accountId, transactionDate, sequenceNumber, primaryTransaction, primaryTransactionSequence, cleared, payeeId, entryType, note)
SELECT T.accountId, S.nextDate, 0, @newEntryId, I.sequence, 0, T.payeeId, T.entryType, T.note
FROM Entry T JOIN EntryItem I ON T.primaryTransaction = I.entryId
JOIN Schedule S ON S.entryId = T.primaryTransaction
JOIN Category C ON I.categoryId = C.id
WHERE T.primaryTransaction = @entryId
AND C.accountId = T.accountId
ORDER BY T.id
-- create transfer entry items
; WITH CurRows (entryId, RowNum)
AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id)
FROM Entry WHERE primaryTransaction = @newEntryId)
, PrevRows (entryId, RowNum)
AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id)
FROM Entry WHERE primaryTransaction = @entryId)
INSERT INTO EntryItem (entryId, sequence, amount, categoryId, note)
SELECT T.entryId, I.sequence, I.amount, I.categoryId, I.note
FROM CurRows T
JOIN PrevRows P ON T.RowNum = P.RowNum
JOIN EntryItem I ON I.entryId = P.entryId
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply