How can I insert new data from one table to another table without inserting any duplicate data?

Viewed 36

I am trying to append new address data from [clients_tbl] to [Address_tbl]. Following is the structure of the Client's table

AutoNo  CNR  FNC        SNC      AM1  AM2 AM3 AM4 AM5   AF1 AF2 AF3 AF4 AF5
   3     1   FirstName  Surname   A1  A2  A3  A4 Zipcode D   A   B   A  Zipcode
   4     2   FirstName1 Surname1  C    D   E   F Zipcode X   Y   Z   A  Zipcode

AM = Address Mother(AM1 = Address1 of mother, AM2 = Address2 of mother, AM3 = Town of mother, AM4 = City of mother, AM5 = Zipcode of mother) AF = Address Father(Same structure as above for father)

Following is the structure of the Address table:

AddrsID CNR RelationID AddressTypeID Address1 Address2 Address3 Address4 Address5
  1      1    4610          1           A1         A2     A3       A4     Zipcode  
  123    1    8653          1            D         A       B       A4     Zipcode

Now the relation ID in the Address table comes from the Relation Table:

RelationID  CNR RelationTypeID     FirstName          Surname   
    1        1         1        First Name          Surname 
  4610       1         2        FirstNameParent 1   SurnameParent1  
  8653       1         3        FirstNameParent 2   SurnameParent2  
  11840      1         4        FirstNameGuarian    SurnameGuarian  

Now imagine that the new data is inserted into the client's table with CNR = 2. I want to insert the Address data into the Address table from the client's table. I wanted to know how can I achieve this. I have tried a few codes but it's not working. Please look at the code below to insert new mother's address data and father's data from client's table to address table:

INSERT INTO [dbo].[1-03_RelationsAddress_tbl]
           ([CNR]
           ,[AddressTypeID]
           ,[Address1]
           ,[Address2]
           ,[Address3]
           ,[Address4]
           ,[Address5]
           ,[LastUpdated]
           ,[UpdatedBy]
           ,[Active])
    Select [CNR]
          ,1
          ,[AM1]
          ,[AM2]
          ,[AM3]
          ,[AM4]
          ,[AM5]
          ,getdate()
          ,124
          ,1
    from dbo.[1-01_Clients_tbl]
    Where ([AM1] is not null or [AM2] is not null or [AM3] is not null or [AM4] is not null or [AM5] is not null)
    AND (dbo.[1-01_Clients_tbl].CNR NOT IN (Select [dbo].[1-03_RelationsAddress_tbl].CNR from [1-03_RelationsAddress_tbl]))
INSERT INTO [dbo].[1-03_RelationsAddress_tbl]
           ([CNR]
           ,[AddressTypeID]
           ,[Address1]
           ,[Address2]
           ,[Address3]
           ,[Address4]
           ,[Address5]
           ,[LastUpdated]
           ,[UpdatedBy]
           ,[Active])
    Select [CNR]
          ,1
          ,[AF1]
          ,[AF2]
          ,[AF3]
          ,[AF4]
          ,[AF5]
          ,getdate()
          ,124
          ,1
    from dbo.[1-01_Clients_tbl]
    Where ([AF1] is not null or [AF2] is not null or [AF3] is not null or [AF4] is not null or [AF5] is not null)
    AND (dbo.[1-01_Clients_tbl].CNR NOT IN (Select [dbo].[1-03_RelationsAddress_tbl].CNR from [1-03_RelationsAddress_tbl]))

But the issue is now when Mother's data is inserted into the Address table, the Father's data is not inserted as the Address table already contains the CNR value. How can I insert only the new data without having to insert duplicates? Also, is it advisable to truncate the Address table whenever we are going to update data from client's table to address table so that we don't have to use any conditions?

Thanks a million in advance.

0 Answers
Related