USE [tempdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Create Destination Address Table. Customers can have several addresses
CREATE TABLE [dbo].[CustomerAddress](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [IdClient] [INT] NOT NULL,
    [Street] [nvarchar] (100),
    [City] [nvarchar] (100),
    [County] [nvarchar] (100),
    [Country] [nvarchar] (100)
 CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Create Destination Customer Table
CREATE TABLE [dbo].[Customer](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](35) NULL,
    [FirstName] [nvarchar](35) NULL
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Create Source Address Table. Customers can have several addresses
CREATE TABLE [dbo].[CustomerAddress2](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [IdClient] [INT] NOT NULL,
    [Street] [nvarchar] (100),
    [City] [nvarchar] (100),
    [County] [nvarchar] (100),
    [Country] [nvarchar] (100)
 CONSTRAINT [PK_CustomerAddress2] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Create Source Customer Table
CREATE TABLE [dbo].[Customer2](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](35) NULL,
    [FirstName] [nvarchar](35) NULL
 CONSTRAINT [PK_Customer2] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


--Add customers and addresses to source and destination tables. Each table will have 3 clients, some with multiple addresses
USE [tempdb]
INSERT INTO [dbo].[Customer] (LastName, FirstName) VALUES ('Johnson', 'James')
INSERT INTO [dbo].[Customer] (LastName, FirstName) VALUES ('Kelly', 'Karl')
INSERT INTO [dbo].[Customer] (LastName, FirstName) VALUES ('Lawlor', 'Liam')
INSERT INTO [dbo].[CustomerAddress] (IdClient,Street,City,County,Country) VALUES (1,'65 North St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress] (IdClient,Street,City,County,Country) VALUES (2,'42 South St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress] (IdClient,Street,City,County,Country) VALUES (3,'12 West St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress] (IdClient,Street,City,County,Country) VALUES (1,'17 East St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress] (IdClient,Street,City,County,Country) VALUES (1,'75 Centre St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[Customer2] (LastName, FirstName) VALUES ('Adams', 'Anne')
INSERT INTO [dbo].[Customer2] (LastName, FirstName) VALUES ('Byrne', 'Bressie')
INSERT INTO [dbo].[Customer2] (LastName, FirstName) VALUES ('Casey', 'Charlene')
INSERT INTO [dbo].[CustomerAddress2] (IdClient,Street,City,County,Country) VALUES (1,'99 North St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress2] (IdClient,Street,City,County,Country) VALUES (2,'88 South St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress2] (IdClient,Street,City,County,Country) VALUES (3,'88 West St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress2] (IdClient,Street,City,County,Country) VALUES (1,'66 East St.','Marcoussin','Jojoba','Flatland')
INSERT INTO [dbo].[CustomerAddress2] (IdClient,Street,City,County,Country) VALUES (1,'55 Centre St.','Marcoussin','Jojoba','Flatland')