How do I update the existing records in SQL table when importing through excel file.

Question

0.00/5 (No votes)

See more:

Hello,

I am developing an upload mechanism, where user will upload excel file into sql table using a button click.
The excel file has columns Ref #, EndDate,StepName and StepResponse.
And my SQL table has columns Ref#, Login_Date, Decision_Date StepName and StepResponse.
The excel file will be uploaded daily in the database.
The Column StepName in excel file can have records like BureauPull or Level2.
Ref# is unique column in excel file. Excel file is being downloaded from another system and it is downloaded and then uploaded into SQL table on daily basis.
Now lets assume, i have a Case with a Ref#='abc123' and StepName = 'BureauPull' and whenever StepName = BureauPull then the EndDate will be mapped with Login_Date in SQLTable and whenever the StepName = Level2 the EndDate will be mapped with Decision_Date.
Now lets say when i download this excel file on the second day and the StepName of same case i.e. Ref#='abc123' changes from 'BureauPull' to 'Level2' so when i upload this file in SQL table then i need to update the previous uploaded case aswell. Now the EndDate will be mapped to Decision_Date.
I hope i have explained it well, if not please don't hesitate to ask and i will try to clear any confusion.
Looking forward to your usual support.

Thanks

What I have tried:

Have searched through internet but unable to find any relevant solution

Posted 12-Jun-17 20:09pm

Faran Saleem

Updated 13-Jun-17 3:13am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2017-06-13T03:13:00

Solution 1

Upload your excel file to a stagetable with the same columns as the Excel file.
Use MERGE[^] to insert or update to the final table.
The next day you truncate the stagetable before inserting the next Excel file.

Posted 13-Jun-17 3:13am

Jörgen Andersson

Comments

Faran Saleem 19-Jun-17 3:32am

Thanks for your reply.
I am trying to do that, i have constructed the following query

MERGE od_test2 T --tareget
USING OD_TEST S --source
ON (S.ref = T.REF)
WHEN MATCHED
THEN UPDATE
SET T.LOGIN_DATE =
(CASE
WHEN s.stepname IN ('BUREAUPULL') THEN CONVERT(VARCHAR,s.[ENDDATE])
ELSE CONVERT(VARCHAR,s.[LOGIN_DATE])
END S.LOGIN_DATE),
(CASE
WHEN s.stepname IN ('l2','l3') THEN CONVERT(VARCHAR,s.[ENDDATE])
ELSE CONVERT(VARCHAR,s.[DECISION_DATE])
END S.DECISION_DATE)

WHEN NOT MATCHED BY TARGET
THEN INSERT (Target_Column_Names)
VALUES (Source_Column_Names)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

But the above query bis giving me syntax error. Can you help me resolve it?
i am trying to use case with UPDATE. When the stepname is BureauPull the EndDate should be inserted in Login_Date and when stepname is L2 or L3, EndDate should get inserted in Decision_Date

Jörgen Andersson 19-Jun-17 4:07am

You only set one target column in the Update Clause. You probably need something like this:
MERGE od_test2 T --target
USING OD_TEST S --source
ON S.ref = T.REF
WHEN MATCHED THEN UPDATE
SET T.LOGIN_DATE = (
CASE
WHEN s.stepname IN ('BUREAUPULL') THEN CONVERT(VARCHAR,s.[ENDDATE])
ELSE CONVERT(VARCHAR,s.[LOGIN_DATE])
END S.LOGIN_DATE
)
,T.DECISION_DATE = (
CASE
WHEN s.stepname IN ('l2','l3') THEN CONVERT(VARCHAR,s.[ENDDATE])
ELSE CONVERT(VARCHAR,s.[DECISION_DATE])
END S.DECISION_DATE
)
WHEN NOT MATCHED BY TARGET
THEN INSERT (Target_Column_Names)
VALUES (Source_Column_Names)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Faran Saleem 19-Jun-17 4:55am

I have tried this but it is giving me syntax error near CASE ending bracket