Looping through table and creating new reocrds

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Looping through table and creating new reocrds

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2008-06-06 : 10:53:39

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
John	8139985434	1
Mary	8139453409	2
Jane	7185432341	3

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
John	8139985434	1
Jane	7185432341	3
Mary	8139453409	5
Mary	8139453409	6

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 11:08:14

[code]INSERT INTO A
SELECT Employee, Phone,6
FROM A
WHERE ID1=2

UPDATE A
SET ID1=5
WHERE ID1=2 [/code]

ninel
Posting Yak Master

141 Posts

Posted - 2008-06-06 : 11:15:08

Thank you for your help visakh16, but I need to maintain table A the way it was originally and add the new records to table B.

Is it actually that simple...no looping?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:05:33

quote:
Originally posted by ninel

Thank you for your help visakh16, but I need to maintain table A the way it was originally and add the new records to table B.

Is it actually that simple...no looping?

INSERT INTO B
SELECT Employee, Phone,ID1
FROM A
WHERE ID1<>2

UNION ALL

SELECT Employee, Phone,5
FROM A
WHERE ID1=2

UNION ALL

SELECT Employee, Phone,6
FROM A
WHERE ID1=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources