import from CSV/EXCEL and insert/update into SQL which is backend for payroll software

  • 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.

  • Hi,

    First you can insert the data of excel file in a SQL table using import task/Bulk Insert/SSIS whatever you like.

    Once you inserted the data into the table you can use the MERGE statement for it.

    like this -

    MERGE MasterpayCard AS T

    USING Excelfiletable AS S ON (T.EmployeeNumber = S.EmployeeNumber AND T.Payrollcategory = S.PayrollCategory)

    WHEN NOT MATCHED BY MasterPayCard

    THEN INSERT (EmployeeNumber, PayrollCategory) VALUES (S.EmployeeNumber, S.PayrollCategory)

    WHEN MATCHED

    THEN UPDATE

    SET T.Payrollcategory = S.Payrollcategory

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Learner44 (6/10/2016)


    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.

    To be clear, the company you work for uses spreadsheet inputs to maintain payroll??? Since that spreadsheet is the critical source of information, how is security being enforced to prevent unauthorized access and how is that spreadsheet being backed up?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Shifting gears to more of a direct focus on your problem...

    You say you have an Excel spreadsheet and then you talk about a ".CSV". Which is it?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I get in EXCEL sheet. I convert them into CSV which gives me bit flexibility to run SSIS package.

  • Learner44 (6/12/2016)


    I get in EXCEL sheet. I convert them into CSV which gives me bit flexibility to run SSIS package.

    Could you attach a sample of the file using the example data you posted just to make sure I have the format of the file correct? And, no... you don't need SSIS for this. Is using SSIS a requirement for this task?

    Also, where is the CSV file stored? Is in on the SQL Server box or is it on a different box? If on a different box, is there a share that SQL Server can "see"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

    this thing worked for me.

  • K. Glad that works for you. I was also going to try to show you how you could import the Excel file without having to convert it to a CSV first. That's why I wanted you to provide a sample Excel file that looks like what you're dealing with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Forever for your Forever help!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply