create a new table from an existing one

[sj1187534]

Hi...I have one more question....

 

Right now, I have a column DLoc in table T1. The DLoc currently has string values separated by a comma...like,

 

T1

======

ID -> 2

DLoc -> Dallas, Boston, Chicago

 

ID is the primary key

==================

 

Now, I want to create a table T2 that has two columns whose values are like this (obtained from T1):

 

T2

======

ID -> 2

Loc -> Dallas

ID -> 2

Loc -> Boston

ID -> 2

Loc -> Chicago

 

(ID,Loc) is the primary key

====================

 

Any ideas on what is the best way to do this??

 

~SJ

[Nerseus]

Is this a one time update (I hope)?

 

You could write some ADO.NET code to loop through each row and parse the string, inserting new records. Or you could use a cursor in SQL Server to do the same thing, thought string parsing and cursors are not SQL Server's strong point (slow and harder to write than "normal" SQL).

 

-Nerseus

[Derek Stone]

Is this a one time update (I hope)?

I hope so too. If this isn't a one time operation such as updating the database or such, then I'd strongly suggest you look into the topic of normalisation. If you're well aware of that term then I digress.

[sj1187534]

yeah...this is a one time thing...i am going to use that table in the future

 

SJ