Copying data from one table to another

[Lisa]

I have a table that contains the first and last name in a single column. I need to copy this data to another table that has separate columns for the first and last name. Can anyone tell me how to do this?

[Karl]

Hi Lisa,

this will only work if the first and last name are seperated by a space (or a comma or other delimiter). It also assumes that there is only one first name (i.e. no middle name) and one surname. In other words, there are only two names. The moment there is more than two names it won't work.

INSERT INTO TABLE2 (FirstName, LastName)
SELECT SUBSTRING(Name,1,CHARINDEX(' ',Name)-1),SUBSTRING(Name,CHARINDEX(' ',Name)+1,LEN(Name))
FROM TABLE1

Hope that helps,

Karl



------------
Lisa at 9/7/01 2:18:53 PM

I have a table that contains the first and last name in a single column. I need to copy this data to another table that has separate columns for the first and last name. Can anyone tell me how to do this?

[Lisa]

Thanks Karl! That did the trick!


------------
Karl at 9/8/01 3:50:47 AM

Hi Lisa,

this will only work if the first and last name are seperated by a space (or a comma or other delimiter). It also assumes that there is only one first name (i.e. no middle name) and one surname. In other words, there are only two names. The moment there is more than two names it won't work.

INSERT INTO TABLE2 (FirstName, LastName)
SELECT SUBSTRING(Name,1,CHARINDEX(' ',Name)-1),SUBSTRING(Name,CHARINDEX(' ',Name)+1,LEN(Name))
FROM TABLE1

Hope that helps,

Karl



------------
Lisa at 9/7/01 2:18:53 PM

I have a table that contains the first and last name in a single column. I need to copy this data to another table that has separate columns for the first and last name. Can anyone tell me how to do this?