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 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply