Q:

update existing column values and insert new in record doesn't exist in SQL when importing from CSV

Can you PLEASE help me on following?
We have payroll software that runs on SQL Server. I have to update certain payroll category from the SQL Server so that it can reflect on the software.
This is my Excel file:
Employee Number  Payroll Category  Rate
------------------------------------------
111111           011               32.21
111111           012               56.23
111111           013               12.52
111111           021               45.21
111112           011               36.21
111112           012               56.23
111112           013               42.54
111112           021               85.21

These are the current values in my database table Masterpaycard
 Employee Number     Payroll Category  Rate
 -------------------------------------------
    111111           011               0.00
    111111           012               0.00
    111111           013               10.25

    111112           011               36.21
    111112           012               12.50
    111112           013               41.25
    111112           021               85.21

So if you see following record is not present in the database, but present in the .CSV, then I have to insert it. 
 111111           021               45.21

Here Employee Number and Payroll Category are FKs from the Employee and Payroll Category tables.
So my final results should look like in the database and in the front end something like this.
Employee Number    Payroll Category    Rate
--------------------------------------------
    111111           011               32.21
    111111           012               56.23
    111111           013               12.52
    111111           021               45.21
    111112           011               36.21
    111112           012               56.23
    111112           013               42.54
    111112           021               85.21

I guess in simple words if payroll category match in MASTERPAYCARD table then just update the category with value from .CSV, and if we can not find Payrollcategory than insert that as new category for that employee and add value too from CSV.
Please help.

A:

This is the type of approach you need to take...
create table #Table1 (
    id int,
    value varchar(10)
)

create table #Table2 (
    id int,
    value varchar(10)
)

insert into #Table1 values (1, 'AAA')
insert into #Table1 values (2, 'BBB')

insert into #Table2 values (1, 'ZZZ')
insert into #Table2 values (3, 'CCC')

select * from #Table1
select * from #Table2

--insert data from Table2 into Table1 if it doesn't already exist in Table1
insert into #Table1 
select #Table2.* from #Table2
left join #Table1 on #Table2.id = #Table1.id
where #Table1.id is null

--update data in Table1 from Table2 if it does already exist in Table1
update #Table1 set value = #Table2.value
from #Table2
left join #Table1 on #Table2.id = #Table1.id
where #Table1.id is not null

select * from #Table1

