February 14, 2008 at 10:59 am
Hi everyone,
I have a table with the following structure and data
-- Step --
Id(PK), RisktID(FK), Description
1 1 This is 1
2 1 This is 2
3 2 This is 3
Basically i want to create a Stored Proc that takes a snapshot of the data based on the RiskID Foreign Key
and inserts new records into the same table.
Example
exec StepSnapshot 1
would produce
-- Step --
Id(PK), RisktID(FK), Description
1 1 This is 1
2 1 This is 2
3 2 This is 3
4 1 This is 1
5 1 This is 2
I can achieve this my simply doing a
CREATE PROCEDURE StepSnapShot
@IN_riskID int
AS
BEGIN
INSERT INTO Step
(RiskID, Description)
SELECT
RiskId, Description
FROM Step
Where RiskId = @IN_riskID
END
However within the stored procedure i want to be able to record the value of the Previous Identity (ID Primary Key) with the new Identity value (ID Primary Key) within a Temp Table
example
-- TempTable --
PreviousId, NewId
1 4
2 5
I'm struggling trying to find away to do this can someone help please?
I came up with this but i cant work how to get the previous Orginal Step ID.
DECLARE @tmpSteps TABLE
(
OriginalStepID int,
InsertedStepID Int,
)
INSERT INTO Step
(RiskID, Description)
OUTPUT null, INSERTED.StepID
INTO @tmpSteps
SELECT
RiskId, DescriptionID
FROM Step
Where RiskId = @IN_riskID
SELECT * FROM @tmpSteps
Help would be greatly appreciated
Thanks
February 14, 2008 at 11:45 am
In the output clause you have access to the inserted and deleted tables. To get the original value you would have to use the deleted table.
e.g.
DECLARE @tTest1 TABLE
(
TestCol int,Amount decimal(10,2)
)
INSERT INTO @ttest1 VALUES (1, 100)
INSERT INTO @ttest1 VALUES (2, 200)
INSERT INTO @ttest1 VALUES (3, 300)
UPDATE TOP(2) @tTest1
SET Amount = Amount * 10 OUTPUT DELETED.*,inserted.*
February 14, 2008 at 1:11 pm
I'm not sure if i understand, i'm not updating or deleting from any tables i'm only inserting new records, so how would i have access to DELETED.* values?
February 14, 2008 at 1:19 pm
Ok, I misunderstood what you are doing. I thought you were updating user ids. When an update occurs the new value goes into inserted and the old value goes into deleted.
February 14, 2008 at 1:27 pm
Cool, so any ideas on how to solve this one? I could do it with a cursor but that would suck i'm sure there is an easier way.
February 14, 2008 at 1:29 pm
Can you not store the previous ID in a separate column in the main table? considering this is now going to generate what looks to be duplicate rows in the main table - it's going to be awfully difficult to derive this otherwise...
Once you do that - it becomes VERY simple
INSERT into MyTable (RiskID,Description,PreviousID)
OUTPUT INSERTED.ID,INSERTED.PreviousID INTO @temptable
Select RiskID,Description,ID
from MyTable where riskID=1
----------------------------------------------------------------------------------
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?
February 14, 2008 at 1:45 pm
Yeah that would work, the duplicate data is not a problem but i can't add another column to the table 🙁
February 14, 2008 at 2:13 pm
luke.ryan (2/14/2008)
Yeah that would work, the duplicate data is not a problem but i can't add another column to the table 🙁
Well - I meant it's a problem in that it makes it almost impossible to know WHICH row it came from. To see what I mean - think about what happens the SECOND time you run that against table. You now have :
1 1 This is 1
2 1 This is 2
4 1 This is 1
5 1 This is 2
--------------------------
6 1 This is 1
7 1 This is 2
8 1 This is 1
9 1 This is 2
The last four would be inserted based on what is in the table for rowID=1. Now - there's no way to know for a fact WHICH "This is 1" row was the basis for row #6 unless the insert had an "order by" on it. Without that, it would then be impossible to know conclusively which row was the ancestor.
You might need to do the insert to the temp table FIRST, and then the other insert second using an ORDER BY, and then try to slap it together afterwards.
Perhaps:
select *,0 as previousID into #temptable
from MyTable
where RiskID=1;
INSERT into MyTable (RiskID,Description,PreviousID)
Select RiskID,Description,ID
from #temptable
update t
set t.previousID=Mytbl.ID
from
(select *, ROW_NUMBER() over (order by ID desc) RN from #temptable) as t
inner join
(select *, ROW_NUMBER() over (order by ID desc) RN from MyTable where riskID=1) MyTbl
ON t.riskID=Mytbl.riskID and t.rn=mytbl.rn
don't think this will scale well...
----------------------------------------------------------------------------------
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?
February 14, 2008 at 2:43 pm
Wow what a pain.. Thanks for your suggestion though, what do you think would scale better, that or using a cursor?
February 14, 2008 at 2:58 pm
Well - neither is going to do incredibly well if you consider that this has exponential growth (2 then 4 then 8 then 16 then 32) written all over it for subsequent executions. In 20 runs for RiskID=1, you're up to inserting 500K rows for that run. (EDIT: my math is off: starting with 2 rows - 20 execution runs means you're inserting 2.1Million rows).
that being said - with the right indexes I'd assume mine would do better, at least for the next several runs before you blow the tempDB out of disk space...:)
----------------------------------------------------------------------------------
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?
February 14, 2008 at 3:06 pm
haha thanks for your help. I appreciate it.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply