CODEWITHSUNDEEP
sqlsql-server
tavier
tavier

Reputation: 1794

Copy partial data from one SQL DB to another

I need to copy some of the data from one table in one db to another db. So I have a db1 with table1 with some columns say column1 (unique id), column2, column3 etc. Now I have another db which is a copy of the first one (schema is copied and it will also have some of the data preexisting from db1).

so db1/table1 has records:

column1          column2                   column3
1          12/07/2021 11:60:32         10/03/2021 01:34:00
2          02/02/2021 15:30:32         11/01/2021 15:31:32
3          12/03/2021 17:20:32         13/02/2021 15:30:32
4          12/04/2021 13:40:32         16/08/2021 15:30:34
5          12/06/2021 19:10:32         17/03/2021 15:20:31

db2/table2 has below data:

column1          column2                   column3
1                 null                      null
2          02/03/2021 15:30:32              null
5          22/05/2021 15:30:32         12/07/2021 15:30:32
6          22/05/2021 15:30:32         12/07/2021 15:30:32
7          22/05/2021 15:30:32         12/07/2021 15:30:32

I would like to copy data from db1/table1 to db2/table2 except for those which are not present in db2/table2 (based on the column1 used as identifier). So in this example, records with column1 value 3 and 4 should not be copied into db2/table2.

I am able to get it to work if all records are present in db2/table2 but when the records are missing it runs into error saying no records exist. Sorry if its a trivial one, I am not too good with SQL at all and trying to figure out a way to do it. Thanks.

Upvotes: 1

Views: 385

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you wanted to copy the rows that don't exist in table2, then you would use not exists:

insert into db2.table2 (col1, col2, col3)
    select t1.col1, t1.col2, t1.col3
    from db1.table1 t1
    where not exists (select 1 from db2.table2 t2 where t2.col1 = t1.col1);

The above seems sensible. But if you want to update the values in table2 only for matches, then use update with a join:

update t2
    set t2.col2 = t1.col2,
        t2.col3 = t1.col3
    from table2 t2 join
         table1 t1
         on t2.col1 = t1.col1;

Upvotes: 1

Related Questions