Insert data from a table | SQL Server Performance Forums

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