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
- Split One table into Two in SQL Server 2008
- How to split one column into two columns in SQL Server
- SQL Server : Insert two stored procedure results into one table
- How to split results from one field into two separate fields in SQL Server 2014
- SQL Server 2008 R2: Split column into two
- Copy all matching rows into one cell in another table in SQL Server 2008
- how to split a multiple character string into table in SQL server 2008
- How to merge the two table columns into one table in SQL Server
- Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server
- Inserting data from two similar tables into one master table in Sql Server
- How to turn one column of a table into a csv string in SQL Server without using a cursor
- How to insert one column into other column within the same table in SQL Server
- Insert into table with select statement plus additional column in SQL Server 2008
- How to split string and insert values into table in SQL Server
- Split string into table given row delimiter and column delimiter in SQL server
- Split date range into one row per month in sql server
- How to join result of two sql statements into one table and different columns
- Update one column in a trigger after updating only particular column in the same table in SQL Server 2008
- SQL Server 2008 - Bulk Insert a whole text file into one field
- Is there an elegant way to track the modification of all columns of one table in SQL Server 2008
- Where I can check user-define table type in SQL Server 2008 in SSMS? and how can I insert new column into it?
- Merge two Table in SQL Server 2008
- SQL : Split one row into two rows depending on column
- SQL Server : split one row into many rows (value)
- Table rows with identifying parameter in each row SQL SERVER 2008 into single row
- SQL Server : split multiple strings into one row each
- Split one column into multiple columns in SQL Server
- SQL Server Split one column into multiple columns
- How to write one trigger to all table to SQL Server 2008
- Inserting data into a sql server 2008 table
More questions with similar tag
- Google search API versus MS SQL Server Full Text Indexing?
- CrystalReportViewer.SelectionFormula IN() function
- SQL query to show a portion of string value from a column
- SQL Bulk insert: update rows
- Catching error message from SQL Server in VBA in Excel
- Check if value is in different table
- Precise sort order of records that were found in a IN split function in SQL Server
- Reporting Services Report Builder, get a specific number and add to all other lines
- Nested groups and counts within SQL
- PHP SQLSRV: passing a string of values to IN clause
- Sql server default language list
- SQL Server not difference between 'ی' and 'ي' in Arabic_CI_AS collation
- EntityFramework 6.1.1 with Linq Performance issue
- ASP.NET Identity 2.0 Roles.IsUserInRole gives {"Invalid object name 'dbo.aspnet_SchemaVersions'."} exception
- How to exclude some (not all) record that has same values
- Deploy the database to Docker Container microsoft/mssql-server-linux
- Add Identity column to a view in SQL Server 2008
- Get Table Columns With Primary Key Constraints
- System.Data.SqlClient.SqlException: Invalid object name 'dbo.Slider'
- Is a join to a look-up table more optimal than a user defined scalar function?
- Is UUID v3 insecure for generating surrogate keys for APIs since it is based on MD5?
- SQL Server - count how many names have 'A' and how many have 'E'
- VBA in Access 2010 - Run-time Error 430
- Cursors are taking time on my Stored Procedure?
- Login failed for user during update-database
- Incorrect syntax near ')' calling stored procedure with GETDATE
- How to conditionally assign values in SQL using case
- Is there a way to switch server context within a t-sql script?
- How much performance do I lose by increasing the number of trips to SQL Server?
- Making sense of the number of reads/writes in SQL Profiler
Source:
stackoverflow.com