Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Inserting Records into Temp table

Posted on 2006-03-31
16
Medium Priority
?
381 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:vallury
  • 8
  • 3
  • 3
  • +2
16 Comments
 

Author Comment

by:vallury
ID: 16345425
Anone please help this is very very urgent.
0
 

Expert Comment

by:bnrtech
ID: 16345531
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;
0
 

Author Comment

by:vallury
ID: 16345631
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

)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

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

Author Comment

by:vallury
ID: 16346161
@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
0
 

Author Comment

by:vallury
ID: 16346232
Experts,

Please help this is very very critical
0
 

Assisted Solution

by:bnrtech
bnrtech earned 200 total points
ID: 16346631
I am not sure of the practicality of your request. If you are using the data for a report, just create a SQL view and use that for the reports data source.  

The only way that I can think of at the current time to create the table as you have asked is to create a stored procedure, looping through the records and assigning the fields based on record count varable.

For example

// Denotes where you should enter your fields

DECLARE mytemp_cursor CURSOR FOR
            //  Your Select Statement  
           OPEN mytemp_cursor
           FETCH NEXT FROM mytemp_cursor -- Perform the first fetch.
           INTO // your fields
 
           WHILE @@FETCH_STATUS = 0 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
           BEGIN
         
          //Create table your temp table  
          // Use array for record count and field assignment  array item #1 could be customerid, array item 2 could be number of times student record
              is associated with customer.
          // Insert SQL statement
 
          FETCH NEXT FROM mytemp_cursor -- Perform the next fetch.
            INTO // your fields
     END
     CLOSE mytemp_cursor
     DEALLOCATE mytemp_cursor

Sorry I could not be of more help.
0
 

Author Comment

by:vallury
ID: 16346852
Thanks for your help bnrtech  but this is not actually what I need.
But thanks again
0
 
LVL 10

Expert Comment

by:randeeps
ID: 16346912
@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?
0
 

Author Comment

by:vallury
ID: 16347014
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
0
 
LVL 10

Expert Comment

by:randeeps
ID: 16347212
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!
0
 

Author Comment

by:vallury
ID: 16347226
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.

0
 
LVL 10

Expert Comment

by:randeeps
ID: 16347354
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
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 1400 total points
ID: 16348145
OK, this isn't pretty, but it starts with the tables you gave and gives the output you want.

SELECT CM.Customer_ID, CM.First_Name, CM.Last_Name,
      St1.Student_ID as StudentID1, St1.First_Name as St_FN1,st1.Last_Name as ST_LN1,
      St2.Student_ID as StudentID2, St2.First_Name as St_FN2,st2.Last_Name as ST_LN2,
      St3.Student_ID as StudentID3, St3.First_Name as St_FN3,st3.Last_Name as ST_LN3

FROM (
SELECT Customer_ID,
      MAX(CASE WHEN Pos = 1 then Student_ID ELSE 0 END ) AS STUD1,
      MAX(CASE WHEN Pos = 2 then Student_ID ELSE 0 END ) AS STUD2,
      MAX(CASE WHEN Pos = 3 then Student_ID ELSE 0 END ) AS STUD3
FROM (
SELECT  customer_ID, Student_ID, (SELECT COUNT(*) From Student_Master S1 WHERE S1.Customer_ID = S0.Customer_ID and S1.Student_ID <= S0.Student_ID) as Pos
FRom Student_Master S0
GROUP BY Customer_ID, Student_ID
) StudMaster
Group By Customer_ID
) Cust_Stu
INNER JOIN Customer_Master CM ON CM.Customer_ID = Cust_Stu.Customer_ID
LEFT OUTER JOIN Student_Master St1 ON St1.Student_ID = Cust_Stu.Stud1
LEFT OUTER JOIN Student_Master St2 ON St2.Student_ID = Cust_Stu.Stud2
LEFT OUTER JOIN Student_Master St3 ON St3.Student_ID = Cust_Stu.Stud3


EXPLANATION:

To start, we need to know the number of students for each customer
SELECT  customer_ID, Student_ID, (SELECT COUNT(*) From Student_Master S1 WHERE S1.Customer_ID = S0.Customer_ID and S1.Student_ID <= S0.Student_ID) as Pos
FRom Student_Master S0
GROUP BY Customer_ID, Student_ID

This gives a table like:
CusID StudID Pos
1      1      1
1      2      2
1      3      3
2      4      1

Then we use Pos field to do a crosstab query using the above query as a source:
SELECT Customer_ID,
      MAX(CASE WHEN Pos = 1 then Student_ID ELSE 0 END ) AS STUD1,
      MAX(CASE WHEN Pos = 2 then Student_ID ELSE 0 END ) AS STUD2,
      MAX(CASE WHEN Pos = 3 then Student_ID ELSE 0 END ) AS STUD3
FROM (
SELECT  customer_ID, Student_ID, (SELECT COUNT(*) From Student_Master S1 WHERE S1.Customer_ID = S0.Customer_ID and S1.Student_ID <= S0.Student_ID) as Pos
FRom Student_Master S0
GROUP BY Customer_ID, Student_ID
) StudMaster
Group By Customer_ID

Giving results of:
CusID Stud1 Stud2 Stud3
1      1      2      3
2      4      0      0

I use 0 to mean no students for that position

next, wrap it all together to get the data you requested.  I left outer join the student_master in to each student ID (in case some customer has no students, he'll still show).

Not pretty, but it works
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 400 total points
ID: 16348905

create table Customer_Student_Master(
Customer_ID  int ,
First_name  varchar(100),
Last_Name  varchar(100),
Student_ID_1  int,
Student_Fname_1 varchar(100),
STudent_LName_1s varchar(100),
Student_ID_2  int,
Student_Fname_2  varchar(100),
STudent_LName_2  varchar(100),
Student_ID_3  varchar(100),
Student_Fname_3  varchar(100),
STudent_LName_3  varchar(100))

truncate table Customer_Student_Master
declare @customer_id int,
      @first_name varchar(100),
      @last_name varchar(100),
      @student_id int,
      @student_fname varchar(100),
      @Student_lname varchar(100)
declare c cursor for select customer_id, first_name, last_name, student_id, student_fname, Student_lname from Customer_Master inner join Student_Master on Student_Master.customer_id = Customer_Master.customer_id

open c
fetch next from c into @customer_id ,@first_name ,@last_name,@student_id,@student_fname,@Student_lname

while @@fetch_status = 0
begin



      if exists (select * from Customer_Student_Master where customer_id = @customer_id)
      begin
            if exists (select * from Customer_Student_Master where customer_id = @customer_id and Student_ID_2 is null)
            update Customer_Student_Master set Student_ID_2 =@student_id,
                                    Student_Fname_2=@student_fname,
                                    STudent_LName_2=@Student_lname where customer_id = @customer_id
            else
            update Customer_Student_Master set Student_ID_3 =@student_id,
                                    Student_Fname_3=@student_fname,
                                    STudent_LName_3=@Student_lname where customer_id = @customer_id

      end
      else
      insert into Customer_Student_Master(Customer_ID,First_name,Last_Nameas,Student_ID_1,Student_Fname_1,STudent_LName_1)
      values(@customer_id ,@first_name ,@last_name,@student_id,@student_fname,@Student_lname )
      
fetch next from c into @customer_id ,@first_name ,@last_name,@student_id,@student_fname,@Student_lname
      
end
close c
deallocate c
go

select * from Customer_Student_Master
0
 

Author Comment

by:vallury
ID: 16356684
lludden  and imran_fast  and everyone.

thanks a ton for your help.

Thanks again

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question