SQL Server: Extracting a Column Into a Table

Question

I have a table with a column that I want to extract out and put into a separate table.

For example, lets say I have a table named Contacts. Contacts has a column named Name which stores a string. Now I want to pull out the names into another table named Name and link the Contact.Name column to the Id of the Name table.

I can only use SQL to do this. Any ideas on the best way to go about this?

Let me know if I can clarify anything, thanks!

[edit] One problem is that different contacts can be tied to the same name. So when different contacts have the same name and it gets exported the Name table would only have one unique row for that name and all the contacts would point to that row. I guess this wouldn't make sense if I were actually working on a contact book, but I'm just using it to illustrate my problem.

Answer 1

insert into another_table( contact_id, name )
select id, name 
from contacts;

Answer 2

Create the new table with a Foreign key that points back to the contact table. Then insert the names and contactids from the contact table into this new table. After that you can drop the "name" column from the contact table.

CREATE TABLE Name
(
   ContactId int,
   Name nvarchar(100)
);

INSERT Name(Name)
SELECT ContactId, Name From Contact;

ALTER TABLE Contact
DROP Column name;

EDIT: Since you have edited the question to mention that one name can be associated with multiple contacts, this changes things in the opposite way.

CREATE TABLE Name
(
   NameId int IDENTITY,
   Name nvarchar(100)
);

INSERT Name(Name)
SELECT DISTINCT Name From Contact;

ALTER TABLE Contact
ADD NameId int;

UPDATE c
SET c.NameId = n.NameId
FROM Contact c
JOIN Name n on n.Name = c.Name;

ALTER Table Contact
Drop Column Name;

NOTE: Make sure that you create the appropiate foreign key between the Contact and Name tables using the NameId on the Contact table and also create a UNIQUE constraint on the "name" column in the Name table.

Answer 3

CREATE TABLE Name (NameID int IDENTITY(1, 1), [Name] varchar(50))

INSERT INTO Name ([Name])
SELECT DISTINCT [Name]
FROM Contact

ALTER TABLE Contact
    ADD COLUMN NameID int

UPDATE Contact
SET NameID = [Name].NameID
FROM Contact
INNER JOIN [Name]
    ON Contact.[Name] = [Name].[Name]

ALTER TABLE Contact
    DROP COLUMN [Name]

Then add foreign key constraint, etc.

Answer 4

insert into new_table (contact_id, name) select min(id), name from contacts group by name;

This is one way of ensuring only one row per name - you can substitute other functions for min (like, for eg max).

I'm not too sure why you would want to do this, though. No matter what, you will end up with some contacts that don't have a name linked to them...

Answer 5

ALTER TABLE `Contacts` ADD `name_id` INT( 12 ) NOT NULL 
ALTER TABLE `Name` ADD `Name` VARCHAR( 200 ) NOT NULL 
INSERT INTO Name (id, name) SELECT id, Name FROM Contacts
ALTER TABLE `Contacts` DROP `Name`

The problem is the name_id field, which is filles with "0" and should be have the same value as the id in the Contacts-Table. Here you can use the LOOP or ITERATE statement (if you using MySQL).