SQL Server inserts new rows only based on multiple columns

advertisements

I searched in SO but couldn't find anything for my purpose. I need to insert unique rows ONLY from one table into another. I have:

table1

id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1331   elm       04/01/2016  04/30/2016 05/01/2016 5656

table2

id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1441   elm       04/01/2016  04/30/2016 05/01/2016 5656

I need to insert into table1 the row with id = 2 in table2 AND also delete the row with id = 2 from table1, because bookid is different even though the rest of the columns match. I tried following:

insert into table1
select * from table2
where not exists (select * from table2 where table1.id = table2.id
and table1.name = table2.name and table1.bookid = table2.bookid and
table1.bookname = table2.bookname and table1.start_date =  table2.start_date
and table1.end_date = table2.end_date and table1.rel_date =            table2.rel_date
and table1.rel_id = table2.rel_id)

Any way I can do all of this in one sql block?


In theory the following merge statement should achieve what you are looking for.

    MERGE table1 [Target]
    USING table2 [Source]
    ON ([Target].[name] = [Source].[name]
            AND
        [Target].[bookname] = [Source].[bookname]
            AND
        [Target].[start_date] = [Source].[start_date]
            AND
        [Target].[end_date] = [Source].[end_date]
            AND
        [Target].[rel_date] = [Source].[rel_date]
            AND
        [Target].[rel_id] = [Source].[rel_id]
      )
    WHEN MATCHED AND ([Target].[bookid] <> [Source].[bookid]) THEN
        UPDATE
            SET  [Target].[name]             = [Source].[name]
                ,[Target].[bookid]           = [Source].[bookid]
                ,[Target].[bookname]         = [Source].[bookname]
                ,[Target].[start_date]       = [Source].[start_date]
                ,[Target].[end_date]         = [Source].[end_date]
                ,[Target].[rel_date]         = [Source].[rel_date]
                ,[Target].[rel_id]           = [Source].[rel_id]
    WHEN NOT MATCHED  THEN
    INSERT(
             [name]
            ,[bookid]
            ,[bookname]
            ,[start_date]
            ,[end_date]
            ,[rel_date]
            ,[rel_id]
         )
    VALUES
    (
             [Source].[name]
            ,[Source].[bookid]
            ,[Source].[bookname]
            ,[Source].[start_date]
            ,[Source].[end_date]
            ,[Source].[rel_date]
            ,[Source].[rel_id]
    );

Note that there are some risks and limitations to this approach. If your [id] column has a uniqueness constraint, then it should be set as an identity column otherwise you will run into uniqueness violation errors. Also if [id] column value in table1 is different to [id] column in table2 then merge statement will keep the original [id] value from table1.

Basically this query simply updates your existing record in table1 with the matching record in table2 and insert new records from table2 into table1 if they don’t already exists.