SQL Server Performance Forum – Threads Archive

Insert data from a table

Hi, I would like to insert data from a table into a new table. Ex: Table A contactId, BusinessPhone, HomePhone, HomePhone, etc ( I am going to use only few of these columns and some like homefax, businessFax as Fax and other fax) Table B contactId, NumberType, Number, Extension while moving data i would like to remove any characters mentioned along with the numbers like ‘X’, ‘extn’, and move the numbers after the extn into the extention column.
I also have 2 numbers in the same column actually seperated by a column but when i cleanced the data in that table now they look like a single number. I would like to move the 2nd number after the 10 digits into other row along with the different number type. Could someone help me out with a querry. Your help is highly appreciated. Thanks in advance, Madduri

---

Step 1:
Create the target table with the correct column sizes. Step 2:
Create a SELECT query that uses LEFT, RIGHT and SUBSTRING to take the existing data, and presents the new data in its new format. Step 3:
When your SELECT query is returning the desired output, add an INSERT INTO clause in front of it, and execute.

---

If the data is huge, you might want to add the indexes/FK/constraints after your data is moved. You might also want to consider using BCP utility if the data is in millions of rows. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

]]>