none
Insert Records that exist in one table that are not in another RRS feed

  • Question

  • I have 2 tables. One is a master table and the other is a temporary table that is used for importing data. There will be times when the import file will contain a part # that we do not have in the master table, so it will need to be added. Below is an example of how I would need it to be setup.

    PartNumber	ImportTable
    A1		A1
    A2		A2
    A3		A7
    A4
    A5

    With the above, I would need A7 to be added to the main table. Any assistance would be appreciated.

    Monday, April 21, 2014 12:18 PM

Answers

  • select PartNumber from ImportTable it where

    not exists 

    (

    select 1 from MainPartTable mpt where it.PartNumber = mpt.PartNumber

    )

    You will get the part number which are not in Main table.

    Monday, April 21, 2014 12:43 PM
  • Hi DanHaf,

    Please refer to the following sample code:

    insert into MasterTable
    select * from TemporaryTable p
    where NOT EXISTS(
        SELECT * FROM MasterTable WHERE PartNumber=p.PartNumber
    );

    You can also take a look at Merge() function:
    Inserting, Updating, and Deleting Data by Using MERGE: http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, April 23, 2014 2:42 AM
    Moderator
  • create table masters  (PartNumber  char(2))
    create table ImportTable (PartNumber char(2))
    insert into masters values('A1'),('A2'),('A3'),('A4'),('A5')
    insert into ImportTable values('A1'),('A2'),('A7')
    
    
    Merge masters m
    using Importtable i on m.PartNumber=i.PartNumber
    When Not matched Then
    Insert (Partnumber) values (i.partNumber);
    
    Select * from masters
    
    drop table masters, ImportTable

    Wednesday, April 23, 2014 3:05 AM
    Moderator

All replies

  • select PartNumber from ImportTable it where

    not exists 

    (

    select 1 from MainPartTable mpt where it.PartNumber = mpt.PartNumber

    )

    You will get the part number which are not in Main table.

    Monday, April 21, 2014 12:43 PM
  • Hi DanHaf,

    Please refer to the following sample code:

    insert into MasterTable
    select * from TemporaryTable p
    where NOT EXISTS(
        SELECT * FROM MasterTable WHERE PartNumber=p.PartNumber
    );

    You can also take a look at Merge() function:
    Inserting, Updating, and Deleting Data by Using MERGE: http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, April 23, 2014 2:42 AM
    Moderator
  • create table masters  (PartNumber  char(2))
    create table ImportTable (PartNumber char(2))
    insert into masters values('A1'),('A2'),('A3'),('A4'),('A5')
    insert into ImportTable values('A1'),('A2'),('A7')
    
    
    Merge masters m
    using Importtable i on m.PartNumber=i.PartNumber
    When Not matched Then
    Insert (Partnumber) values (i.partNumber);
    
    Select * from masters
    
    drop table masters, ImportTable

    Wednesday, April 23, 2014 3:05 AM
    Moderator