Get Data from Excel store to db in different tables

Question

0.00/5 (No votes)

See more:

Im having some data in excel say:
1 aa bb cc
2 dd ee ff

The first two columns have to be stored to master table in database(MS Sql) and the other two , to a child table which is related using foreign key. How can we append this data to mastertable and child table. If the master table already contains data it will append the first column since its auto incremented. eg
I have master table with data
1 xx
.. ..
.. ..
100 pp
so i want to store next- 1 aa below this, so the 1 will not be entered as 1 but 101, it will be
101 aa
and in child table
101 ee ff
How can I achieve this, separate data, put in two different tables and maintain the foreign key relation?

Posted 19-Aug-14 20:03pm

Member 10990313

Add a Solution

Comments

Mayank Vashishtha 20-Aug-14 2:30am

OK. So where are you stuck? Kindly elaborate.

Star_Rookie 20-Aug-14 2:58am

Well I am also a tad unsure as to exactly what your question is, but as I understand it, you are trying to add a key manually (the foreign key i presume...?) depending on how your stuff looks, it could be resolved by saying "SELECT TOP 1 WHERE foreignKey IS NULL" but I am not sure if this is of any use as I am not sure what you are asking, and the example is just a thought that might get you started? I hope anyways. =)

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)

ChauhanAjay · Answer 1 · 2014-08-19T21:38:00

Please use the following steps.

1. Read the uploaded excel file to the datatable.
2. Run the loop for the datatable.
3. Build an insert statement with the columns of the master table and store it in a variable.
4. Build an insert statement with the columns of the child table and store it in a variable.
5. Execute the above queries after the datatable loop.