How to generate data from a table to another table?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

I have a table in database called Employee which has 4 columns namely
*EmployeeID
*EmployeeName
*Designation
*Reporting Manager
which gets updated with new details everytime a new record is added in aspx page.
EXAMPLE:

EID     EMPLOYEE_NAME     DESIGNATION       REPORTING_MANAGER
1        TIM COOK            CEO                   Null
2        MILLER              CTO                   TIM COOK
3        BRAD                COO                   TIM COOK
4        WILLIAM             HRD                   TIM COOK
5        JOHN                TECH MANAGER(DEV)     WILLIAM
6        WALLACE             TECH MANAGER(TEST)    WILLIAM
7        SUNDAR              SWIFT DEV             JOHN
8        PETER               SWIFT DEV             JOHN
9        STEVE               TEST ENGINEER         WALLACE

i Wish to generate a table from above table like the one below,

EID     EMPLOYEE_NAME      REPORTING_MANAGER_ID
1        TIM COOK          Null
2        MILLER            1
3        BRAD              1
4        WILLIAM           1
5        JOHN              4
6        WALLACE           4
7        SUNDAR            5
8        PETER             5
9        STEVE             6

Please help me out.
Thank in advance.

Posted 9-Sep-15 10:04am

Member 1097736

Updated 9-Sep-15 10:28am

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 9-Sep-15 16:11pm

Where are you stuck?

Member 1097736 10-Sep-15 5:53am

Please help me provide the query ryan...

Sergey Alexandrovich Kryukov 9-Sep-15 16:19pm

What have you tried so far?
—SA

Member 1097736 10-Sep-15 5:52am

I have populated data in Employee(table 1) and created a table2 with 3columns as specified above.Now i need to generate data in table2 from the data available in table 1.

Sergey Alexandrovich Kryukov 10-Sep-15 10:44am

I mean, what have you tried for a solution?
—SA

Member 1097736 10-Sep-15 10:49am

I got the solution, Sergey.

Sergey Alexandrovich Kryukov 10-Sep-15 10:51am

Great.

Member 1097736 10-Sep-15 11:06am

Sergey One more help.Hope you could clear this one for me...I have no idea on how to do this.
check this link http://sqllessons.com/categories.html
Look for->Displaying all categories and subcategories: site maps and navigation bars

The result set provided is used to display an unordered list .How do we do it in aspx webforms?

2 solutions

Solution 1

Add column[^] Reporting_manager_id to Employee table, then run below query:

SQL

UPDATE t1 SET t1.Reporting_manager_id = t2.Eid
FROM Employee AS t1 INNER JOIN Employee AS t2 ON t1.Employee_name = t2.Reporting_manager

Now, you can remove Reporting_manager column[^].

Posted 9-Sep-15 10:34am

Maciej Los

Comments

Member 1097736 10-Sep-15 5:43am

Thanks Maciej Los.

My aspx page1 displays Table1's data and i have placed textboxes to add records to it.
My aspx page2 should display Table2.
#YOUR query updates the existing table.But I want to update the details to another table (table 2) using the data in table 1.
So i request you to help me provide queries on how to generate data in Table 2 as well as update it every time a new record is added to Table 1.
Thanks again.

Member 1097736 10-Sep-15 6:26am

And the query you provided is not generating as i expected it results only following.
EID EMPLOYEE_NAME DESIGNATION REPORTING_MANAGER_ID
1 TIM COOK CEO 2
2 MILLER CTO 7
3 BRAD COO NULL
4 WILLIAM HRD NULL
5 JOHN TECHMANAGER(DEV) NULL
6 WALLACE TECHMANAGER(DEV) NULL
7 SUNDAR SWIFT DEV NULL
8 PETER SWIFT DEV NULL
9 STEVE TEST ENGINEER NULL

Maciej Los 10-Sep-15 6:53am

Use the same logic.

Uttam katariya 10-Sep-15 7:44am

hi maciaj
as per your solution
i think its not get perfect result
because inner join can exclude null record so...
Thanks
Uttam

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)

Uttam katariya · Accepted Answer · 2015-09-10T01:46:00

Solution 2

As per maciaj
i think use left join instead of inner join

SQL

UPDATE t1 SET t1.Reporting_manager_id = t2.Eid
FROM Employee AS t1 LEFT JOIN Employee AS t2 ON t1.Employee_name = t2.Reporting_manager

because in employee table some record manager has null so it's not including in result so.......

I hope its helps
Thanks
uttam

Posted 10-Sep-15 1:46am

Uttam katariya

Comments

Member 1097736 10-Sep-15 8:09am

Thanks Uttam it worked out. Do you know how to list the result set in to unordered list in aspx page?
like
*Tim cook
#Miller
#Brad
#William
$John
.sundar
.peter
$Wallace
.steve

Uttam katariya 10-Sep-15 8:19am

sorry please can you explain in detail

Member 1097736 10-Sep-15 8:36am

Sure.
check this link http://sqllessons.com/categories.html
Look for->Displaying all categories and subcategories: site maps and navigation bars

The result set provided is used to display an unordered list .How do we do it in aspx webforms?