asked on

Inserting Records into Temp table

Hello,

I have two tables Customer_Master and Student_Master as shown below

Customer_Master Student_Master
----------------------------------------------------
Customer_ID Student_ID
First_Name Student_Fname
Last_Name STudent_LName
Customer_ID

One customer can have upto 3 students.

I want to create a temp table and insert records into this from the above 2 tables

The temp table should be like this

Customer_ID,First_name,Last_Name,Student_ID_1,Student_Fname_1,STudent_LName_1,Student_ID_2,Student_Fname_2,STudent_LName_2,Student_ID_3,Student_Fname_3,STudent_LName_3

Say if the Customer contains only 1 student record ,Student_ID_2,Student_Fname_2,STudent_LName_2 and ,Student_ID_3,Student_Fname_3,STudent_LName_3 should be null

Please help this is pretty urgent

vallury

ASKER

Anone please help this is very very urgent.

bnrtech

To normalize the database, you can create a table which includes only the Customer_id and Student_id, plus a unique ID for that table. Then remove Customer_ID from the Student Table. Use a sql statement like the following example to view data :

SELECT
FROM (Customer_StudentData LEFT JOIN Customer_Master ON Customer_StudentData.Customer_ID = Customer_Master.Customer_ID) LEFT JOIN Student_Master ON Customer_StudentData.Student_ID = Student_Master.Student_ID;

vallury

ASKER

Thanks for your response..
But what I want is to do is create a temp tbale

Declare @temptab table(
Customer_ID int,
First_name varchar(50),
Last_Name varchar50),
Student_ID_1 int,
Student_Fname_1 varchar(50),
STudent_LName_1 varchar(50),
Student_ID_2 int,
Student_Fname_2 varchar(50),
STudent_LName_2 varchar(50),
Student_ID_3 int,
Student_Fname_3 varchar(50),
STudent_LName_3 varchar(50)

and insert records from

Customer_Master Student_Master
----------------------------------------------------
Customer_ID Student_ID
First_Name Student_Fname
Last_Name STudent_LName
Customer_ID

How do I insert records from the above tables into my temp table.

Thanks

)

bnrtech

Maybe it would help me if I knew what the end result of this temp table is? Are you looking for a report?

vallury

ASKER

@temp table once the records are inserted will like like something below and I am using the same in one of my reports

Customer_ID,First_name,Last_Name,Student_ID_1,Student_Fname_1,STudent_LName_1,Student_ID_2,Student_Fname_2,STudent_LName_2,Student_ID_3,Student_Fname_3,STudent_LName_3

1 John Kim 1 JBC LK 2 KKC PK 3 CCC FK
2 BB CC 5 KK FF Null NUll Null NUll Null null

and so on.

thanks again

vallury

ASKER

Experts,

Please help this is very very critical

SOLUTION

bnrtech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vallury

ASKER

Thanks for your help bnrtech but this is not actually what I need.
But thanks again

randeeps

@vallury - your table design is somwhat improper. If you have a definite no of studentsper customer then you should not be inserting them in a transaction style tyable.

If you can change your design, then this may just suit your better.

customer table:
Customer_ID, FirstName, Lastname, StudentID1, StudentID2, StudentID3

Student Table
StudentID
Firstname
Lastname

This design will prove much more efficient than your previous design.

Any comments?

vallury

ASKER

randeeps
Thanks for your response. The reason wny I am trying to do this is as follows.

I have a .aspx page in which there are form elements for Customer details and Student details. When a user clicks on lookup icon a new window will open and list of customers will be shown in that lookup window. Users when selected one Customer_ID from the lookup page based on the Customer_ID selected I need to populated the Customer details and Student details my ASPX page.

that is the reason why I want all the Customer and student details as one single record.

Thanks

randeeps

I understand that Vallury.. what I would like to know is if the db is still in development stage then why dont you consider change of table design - a better and more efficient table design?

I am still here for a while ... let me know if this is possible. If not then will work on your solution.

Cheers!

vallury

ASKER

Thanks randeeps.

the actual db design is excatly how you suggested. This new table is a temp table I am trying to create and populate records for my other usage.

randeeps

in my design, the students are part of the customer table as Id fields. My design is totally different from yours. Pls check again.

The only solution using your design I can figure out is similar to @bnrtech... i.e. use of cursor. As he has mentioned, you have to loop thru the data and then inserting/updating the temp table. It is not a fancy solution but thats the bestwe can do in this design.

So please think it through and do consider this new design I have put forward.

Cheers

SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vallury

ASKER

lludden and imran_fast and everyone.

thanks a ton for your help.

Thanks again