none
SQL Query Help

    Question

  • Dear Experts,

    Here is my question for you all. I have two Tables A and B. There are 4 columns in A and 4 Columns in B. I want to insert the new data from Table B to Table A. However the insert needs to be based on the Two of the Columns. Basically the two columns will determine if the order is new or not. So, if Column 1 and Column 2 of Table B do not exist in Column 1 and Column 2 of Table A, Then insert the new data into Table A.

    Thank you all for your help.

    Regards,

    Paul

    Saturday, January 25, 2014 1:22 AM

Answers

All replies

  • Try the below:

    Insert into TableA (Col1,Col2,Col3,Col4)

    Select B.Col1,B.Col2,B.Col3,B.Col4 From TableB as B 

    Left Join TableA as A On A.Col1=B.Col1 and A.Col2=B.Col2

    Where A.Col1 is null 

    Saturday, January 25, 2014 1:42 AM
  • I think the JOIN condition operator should be "<>" rather than "=".

    declare @temp1 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp1 values(1,1,5,1)
    insert into @temp1 values(2,1,2,1)
    declare @temp2 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp2 values(1,1,2,1)
    insert into @temp2 values(2,3,2,1)
    insert into @temp1(col1,col2,col3,col4)
    select b.col1,b.col2,b.col3,b.col4 from @temp1 a inner join @temp2 b on a.col1<>b.col1 and a.col2<>b.col2
    ------------------------
    select * from @temp1


    Regards, RSingh


    • Edited by RSingh() Saturday, January 25, 2014 11:35 AM
    Saturday, January 25, 2014 11:35 AM
  • Hello Rsingh, 

    It works with Left Join with IS NULL where condition also. Try the below:

    declare @temp1 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp1 values(1,1,5,1)
    insert into @temp1 values(2,1,2,1)
    declare @temp2 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp2 values(1,1,2,1)
    insert into @temp2 values(2,3,2,1)
    --insert into @temp1(col1,col2,col3,col4)
    select b.col1,b.col2,b.col3,b.col4 from @temp1 a inner join @temp2 b on a.col1<>b.col1 and a.col2<>b.col2
    
    select a.col1,a.col2,a.col3,a.col4 from @temp2 a Left join @temp1 b on a.col1=b.col1 and a.col2=b.col2 where b.col1 is null
    ------------------------
    --select * from @temp1

    Saturday, January 25, 2014 12:37 PM
  • Thank you it looks great. It selects the differanc between temp1 and temp2 based on the first two columns. However I added a few more records to temp2 table and it looks like 4,5,2,1 is repeating two times in the select statement. 

    declare @temp1 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp1 values(1,1,5,1)
    insert into @temp1 values(2,1,2,1)
    declare @temp2 table(col1 int,col2 int,col3 int,col4 int)
    insert into @temp2 values(1,1,2,1)
    insert into @temp2 values(2,1,2,1)
    insert into @temp2 values(2,3,2,1)
    insert into @temp2 values(2,4,2,1)
    insert into @temp2 values(4,5,2,1)
    insert into @temp2 values(2,1,2,1)
    select b.col1,b.col2,b.col3,b.col4 from @temp1 a inner join @temp2 b on a.col1<>b.col1 and a.col2<>b.col2
    

    Monday, January 27, 2014 4:42 AM
  • It needs to only return:

    col1 col2 col3 col4
    2 3 2 1
    2 4 2 1
    4 5 2      1

    But instead it returns:

    col1 col2 col3 col4
    2 3 2 1
    2 4 2 1
    4 5 2 1
    4 5 2 1

    Monday, January 27, 2014 4:45 AM
  • Did you try with LEFT join already suggested?

    select a.col1,a.col2,a.col3,a.col4 from @temp2 a Left join @temp1 b on a.col1=b.col1 and a.col2=b.col2 where b.col1 is null

    Monday, January 27, 2014 4:45 AM
  • Try adding a DISTINCT keywork in the Select List as,

    SELECT DISTINCT b.col1,......................................


    Regards, RSingh

    Monday, January 27, 2014 4:49 AM
  • Thank you this worked
    Wednesday, January 29, 2014 5:30 PM