Looping Through Table and Adding new records

Question

Looping Through Table and Adding new records

ninel

Ten Centuries

Points: 1087
More actions
June 6, 2008 at 8:58 am

#208170

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 3

And another question:

where ID2 5 and 6 come from?

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 4

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