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

Question

Post reply

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

Learner44

SSCarpal Tunnel

Points: 4466
More actions
June 10, 2016 at 3:26 pm

#326872

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.

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

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/

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

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.

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

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.

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

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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.

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

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

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.

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

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 8

Learner44

SSCarpal Tunnel

Points: 4466

June 17, 2016 at 7:27 am

#1885603

Thank you Forever for your Forever help!