score:2

Accepted answer

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

score:0

It would be more normalized if you broke it up into one more table for three total tables. Have the Customers table that has only customer data, have the Address table (which you could possibly rename to State) that has only the Address, then a CustomerAddress table that has both keys to each of those tables as Foreign Keys.

I will start you off to begin:

INSERT INTO Customers (FullName, Telephone)
SELECT DISTINCT FullName, Telephone
FROM Customers_Info

You would do the same for Address. For the 3rd table, you would perform the lookups like this:

INSERT INTO CustomerAddress (CustomerID, AddressID)
SELECT C.CustomerID, A.AddressID
FROM Customers_Info CI
INNER JOIN Customers C
ON CI.Telephone = C.Telephone
INNER JOIN Address A
ON CI.Address = A.Address

More questions

More questions with similar tag