June 6, 2008 at 8:58 am
Table A contains data.
CREATE TABLE A
(Employee varchar(20) NULL,
Phone varchar(10) NULL,
ID1 int )
INSERT A ( Employee, Phone, ID1)
VALUES ('John', '8139985434' , 1)
INSERT A ( Employee, Phone, ID1)
VALUES ('Mary', '8139453409' , 2)
INSERT A ( Employee, Phone, ID1)
VALUES ('Jane', '7185432341' , 3)
Sample table:
Employee Phone ID1
John81399854341
Mary81394534092
Jane71854323413
The table contains 3 records, but if any of the records has an ID of 2 I need to break that record out into 2 records.
Ex: The Mary record has an ID of 2 so I need to create 2 Mary records. One of the Mary records needs to have an ID of 5 and the other 6.
So the result table B would look like this:
Employee Phone ID2
John81399854341
Jane71854323413
Mary81394534095
Mary81394534096
Is there any easy way of doing this? Do I need to loop through table? Can I use something other than a cursor?
I would appreciate any help.
Thanks,
Ninel
June 6, 2008 at 10:12 am
I think you might need to loop here, and you need to be sure that there's an order that ensures that records that could be duplicated appear together so you can handle them. What if there are already 2 records?
There might be a way to do this in SQL, but it could be complicated and hard to understand.
June 6, 2008 at 10:19 am
I can think of a few ways to do this, but I am not sure how solid or complete the rules are. Are we looking for EVERY instance of 2 in ID2,and turning that one row into two rows, always with the values 5 and 6? Is it always 2, or does it happen to be 2 in this case? If so - how would I know which rows to go after. How often does this need to happen? are we essentially looking for this to happen instaneously, or essentially preventing ID2 from ever being 2?
Just trying to get what the overall rule is.
----------------------------------------------------------------------------------
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 7, 2008 at 2:27 pm
June 16, 2008 at 9:58 am
insert A
select Employee, Phone, 5 from A where ID1=2
union all select Employee, Phone, 6 from A where ID1=2
delete from A where ID1=2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply