Split One table into Two in SQL Server 2008

Question

I need to break one table (structure built by someone else but I need the data it contains thousands of records) into two new tables I created.

Table Name: Customers_Info (Old Table)

FullName Telephone Address

Adam Johnson 01555777 Michigan

John Smith 01222333 New York

John Smith 01222333 New Jersey

Lara Thomas 01888999 New Mexico

The above is the old table. Now I created two tables to hold the data, one table for customers with a default address, and the other table to hold additional addresses. In the shown example I need 3 persons to be listed in the Customers table, and the address of "John Smith" (the second one New Jersey) to be listed in the Addresses table. The common field to look at here is "Telephone" and it's unique for every customer.

Here's how the result should be display.

Table Name: Customers (New Table)

CustomerID FullName Telephone Default_Address

1 Adam Johnson 01555777 Michigan

2 John Smith 01222333 New York

3 Lara Thomas 01888999 New Mexico

Table Name: Addresses (New Table)

AddressID CustomerID Address

1 2 New Jersey

Of course it was easy to copy all data into the new Customers table, but what I'm stuck at now, is how to remove the duplicates from Customers and insert them into the Addresses table with the Customer ID and Address only.

Thanks!

ram.bi ram.bi · Accepted Answer · 2014-01-29T18:04:23

Give a try with below code and let me know the comments/results.

CREATE TABLE [Customers_Info]
(
    FullName        VARCHAR(50)
    ,Telephone      VARCHAR(50)
    ,Address        VARCHAR(50)
)

GO

CREATE TABLE Customers 
(
    CustomerID          INT IDENTITY(1,1)
    ,FullName           VARCHAR(50)
    ,Telephone          VARCHAR(50)
    ,Default_Address    VARCHAR(50)
)
GO

ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers
PRIMARY KEY CLUSTERED (CustomerID);
GO

CREATE TABLE Addresses 
(
AddressID       INT IDENTITY(1,1)
,CustomerID     INT
,[Address]      VARCHAR(50)
)
GO

ALTER TABLE dbo.Addresses ADD CONSTRAINT PK_Addresses
PRIMARY KEY CLUSTERED (AddressID);
GO

ALTER TABLE Addresses ADD CONSTRAINT FK_CustomerID_Addresses_Customers FOREIGN KEY (CustomerID)
    REFERENCES dbo.Customers(CustomerID);
GO

INSERT INTO [Customers_Info] VALUES ('Adam Johnson',  '01555777',  'Michigan')
INSERT INTO [Customers_Info] VALUES ('John Smith'  ,  '01222333',  'New York')
INSERT INTO [Customers_Info] VALUES ('John Smith'  ,  '01222333',  'New Jersey')
INSERT INTO [Customers_Info] VALUES ('Lara Thomas' ,  '01888999',  'New Mexico')
INSERT INTO [Customers_Info] VALUES ('Lara Thomas' ,  '01888999',  'New Mexico1')
INSERT INTO [Customers_Info] VALUES ('Lara Thomas' ,  '01888999',  'New Mexico2')
INSERT INTO [Customers_Info] VALUES ('Adam Johnson',  '01555777',  'Michigan1')
INSERT INTO [Customers_Info] VALUES ('Adam Johnson',  '01555777A',  'Michigan')
INSERT INTO [Customers_Info] VALUES ('Adam Johnson',  '01555777A',  'Michigan2')
GO
SELECT * FROM [Customers_Info]

--DELETE FROM Customers
--TRUNCATE TABLE Addresses

------------------------------------------------------------------------------------------------------------------
;WITH a as
(
SELECT FullName,Telephone,[Address],
rn = row_number() over (partition by FullName, Telephone order by FullName)
FROM [Customers_Info]
)

INSERT INTO Customers SELECT 
FullName,Telephone,[Address] from a where rn = 1
------------------------------------------------------------------------------------------------------------------
;WITH b as
(
SELECT FullName,Telephone,[Address],
rn = row_number() over (partition by FullName, Telephone order by FullName)
FROM [Customers_Info]
)

INSERT INTO Addresses SELECT CI.CustomerID,b.[Address] FROM Customers CI 
INNER JOIN b ON b.FullName=CI.FullName AND b.Telephone=CI.Telephone
WHERE b.rn>1


SELECT * FROM Customers
SELECT * FROM Addresses

DROP TABLE [Customers_Info]
GO
DROP TABLE Addresses
GO
DROP TABLE Customers
GO

Split One table into Two in SQL Server 2008

2 Answers