HI! I have and existing table with records already and i want to create a table out of it. To illustrate this is how my existing table looks like the first pic and the second pic is what I will be creating out from the first table.
Is this possible? Kindly please show me how? I am using sql2005 express and I have installed management studio express too.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
First i create the temp table and the extra fields required (one for each subject). Then i select the main data, followed by updates for each subject, using ther IDNO, YR and SUBJECT field for matching it up.
I then select the required data.
Code:
-- DROP table if it exist
--DROP TABLE #temps
-- Create the temp table
CREATE TABLE #temps
(
[UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MI] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[YR] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[RDG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[LANG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MATH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MUSIC] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[ARTS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[GRADE] [int] NULL
)
-- Insert into the temp table
Insert into #temps
(
UKEY,
IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME
)
SELECT UKEY,
IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME
FROM tbl_Test -- **Change this name to match YOUR table name**
-- Update RDG
UPDATE #temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'
-- Update LANG
UPDATE #temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'
-- Update MATH
UPDATE #temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'
-- Update MUSIC
UPDATE #temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'
-- Update ARTS
UPDATE #temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE #temps.IDNO = t2.IDNO
AND #temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'
-- SELECT formatted list
SELECT IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME,
RDG,
LANG,
MATH,
MUSIC,
ARTS
FROM #temps
GROUP BY IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME,
RDG,
LANG,
MATH,
MUSIC,
ARTS
ps. Hopefully i've got the format and fieldnames right for you to run it.
Great! it works perfectly. I have some concerns.
1. Why did you use temporary table, not the normal table?
2. How do incorporate this use this code in vb2005?
3. will it be possible that everytime users will save data to tbl_test the other table (temp table/normal table) will be updated too? (user accessed the table through the network (datatable))?
4. will this work too if the tbl_test columns are not all present in the temp/normal table. ( I mean only selected columns in the tbl_test are in the temp/normal table?
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
Great! it works perfectly. I have some concerns.
1. Why did you use temporary table, not the normal table?
Just in case you where more interested in the output than the extra work i did to arrange the data around. As you want to create a table out if it, i would suggest changing the code to use a normal table. (E.g. Change '#temps' to 'temps')
2. How do incorporate this use this code in vb2005?
I would suggest creating it as a stored procedure and calling that stored procedure via vb2005. (There should be a good few articles in here running Stored Procedures in vb2005)
3. will it be possible that everytime users will save data to tbl_test the other table (temp table/normal table) will be updated too? (user accessed the table through the network (datatable))?
This could be done by setting up an update SQL statement (maybe as a stored procedure) which could be called when users access the table to update the figures. I presume the user accessing it via the network is still through a vb2005 program. You could set it up as an update trigger on the SQL Server Database itself, or run the SQL overnight as a job, using something like SQL Server Agent, or via the command prompt.
4. will this work too if the tbl_test columns are not all present in the temp/normal table. ( I mean only selected columns in the tbl_test are in the temp/normal table?
The tbl_temp table was only created by me help work out how to do the select the way you wanted it. Going forward i would expect that you change the code so that all references pointing to tbl_Test will be change to point at the original table with all the exisiting records in.
Feel free to ask anymore questions
Last edited by kevchadders; Jul 29th, 2008 at 03:20 AM.
I would suggest creating it as a stored procedure and calling that stored procedure via vb2005. (There should be a good few articles in here running Stored Procedures in vb2005)
I afraid my knowlegde for SPROC is not enough for me to come up a good and working SPROC
This could be done by setting up an update SQL statement (maybe as a stored procedure) which could be called when users access the table to update the figures. I presume the user accessing it via the network is still through a vb2005 program. You could set it up as an update trigger on the SQL Server Database itself, or run the SQL overnight as a job, using something like SQL Server Agent, or via the command prompt.
So you are telling that when the user click a save button, aside for my code to save the back the data to the table is another code to update the new table? I haven't used trigger yet.
The tbl_temp table was only created by me help work out how to do the select the way you wanted it. Going forward i would expect that you change the code so that all references pointing to tbl_Test will be change to point at your main table with all the data in.
I am assuming here that even if the two tables are not exactly alike it will still going to work and yes I will be pointing all the references to my own table.
Can you please help me out with the SPROC?
Thanks again. .
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
So you are telling that when the user click a save button, aside for my code to save the back the data to the table is another code to update the new table? I haven't used trigger yet.
Yes you could run another piece of SQL to update the new table. The question is though, do you want to keep this new table for other purposes other that providing a way to view the data better?
If the new table is only there to help with displaying the data, then maybe you could just build up the new table from scratch whenever you need to view the data in your new format. That way you dont have to worry about updates.
I am assuming here that even if the two tables are not exactly alike it will still going to work and yes I will be pointing all the references to my own table.
Yes, hopefully i have got all the field names right in my original example so it should be easy to point all references to you own table.
Can you please help me out with the SPROC?
To create the SQL as a stored procedure. (based on original example with the drop table SQL at the start of the stored procedure)
COPY and PASTE this into a new query window and execute it. You should then see a stored procedure called usp_CreateNewTable located in the Programmability > Stored Procedure Folder within your Database.
To run via SQL just type usp_CreateNewTable
Code:
CREATE PROCEDURE [dbo].[usp_CreateNewTable] AS
-- DROP table if it exist
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[temps]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[temps]
-- Create the temp table
CREATE TABLE temps
(
[UKEY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[IDNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[LASTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[FIRSTNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MI] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[GENDER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[YR] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[SECTIONNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[RDG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[LANG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MATH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[MUSIC] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[ARTS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[GRADE] [int] NULL
)
-- Insert into the temp table
Insert into temps
(
UKEY,
IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME
)
SELECT UKEY,
IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME
FROM tbl_Test -- **Change this name to match YOUR table name**
-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'
-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'
-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'
-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'
-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'
-- SELECT formatted list
SELECT IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME,
RDG,
LANG,
MATH,
MUSIC,
ARTS
FROM temps
GROUP BY IDNO,
LASTNAME,
FIRSTNAME,
MI,
GENDER,
YR,
SECTIONNAME,
RDG,
LANG,
MATH,
MUSIC,
ARTS
Last edited by kevchadders; Jul 29th, 2008 at 07:33 AM.
I haven't tried your post above as i was trying to work on your first post using my own table but i have not succeded yet. I am not able to insert (get) the grade from my source table to the target table. Here is my script for my source table
the first code, i get 20 records-- (20 row(s) affected). and yes the select part of the statement is populating correctly this is what I get (30339 row(s) affected).
In my code, only the FG1 column has no value
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
So i presume the two updates in your example return 0 rows?
If so turn those updates in SELECT statements for testing purposes to see if it helps you to understand whats going wrong.
eg. You could try the select 3 times with different parts of the statement commented out.
Code:
-- Select for English (on ID join only)
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
--AND temp.YEARLEVEL = t2.YEARLEVEL
--AND t2.SUBJECTNAME LIKE 'ENGLISH%'
Code:
-- Select for English (ID and YEARLEVEL)
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
AND temp.YEARLEVEL = t2.YEARLEVEL
--AND t2.SUBJECTNAME LIKE 'ENGLISH%'
Code:
-- This one should bring back the amount of rows which will get updated to the TEMP table in the UPDATE statement
SELECT t2.FG1, t2.IDNO, t2.YEARLEVEL, t2.SUBJECTNAME, *
FROM tblENROL1 t2
WHERE temp.IDNO = t2.IDNO
AND temp.YEARLEVEL = t2.YEARLEVEL
AND t2.SUBJECTNAME LIKE 'ENGLISH%'
Are any of those three examples returning rows?
ps. Make sure there is data in the temp table (based on the initial INSERT above the update statements)
I need to get the grade of each of the subjects of the student and put it in my report so that I can have a produce a report card that will show all the grade of the student. Because with my current table (the first picture in my first post) I am not able to display the grade of a student for all the subjects he/she is enrolled in.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
Why are you creating a table that contains the same data as another table??
Why not just use a VIEW of the existing table and use GROUP BY with CASE/WHEN statements to accomplish the column organization?
How many columns can this get to be - I see RDG1 and RDG2 - is there also a RDG3?
If the amount of columns is somewhat variable then this shouldn't be in your database but should instead be done client side.
first of all I dont know anything about VIEW and second of all, I have rdg1 up to rdg6 and there are other subjects i have in my existing table to wit: English1 to english4, Math1 to Math4, Science1 to Science4 and many others.
As I mentioned in my last post I will be using the table to generate a report card.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
I would like this part of the code to be SPROC and then use it vb.net 2005 on click of a button. How is that possible?
Code:
-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'
-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'
-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'
-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'
-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
CREATE PROCEDURE usp_Update_Subjects
AS
-- Update RDG
UPDATE temps
SET RDG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'RDG%'
-- Update LANG
UPDATE temps
SET LANG = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'LANG%'
-- Update MATH
UPDATE temps
SET MATH = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MATH%'
-- Update MUSIC
UPDATE temps
SET MUSIC = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'MUSIC%'
-- Update ARTS
UPDATE temps
SET ARTS = t2.GRADE
FROM tbl_Test t2
WHERE temps.IDNO = t2.IDNO
AND temps.YR = t2.YR
AND t2.SUBJECT LIKE 'ARTS%'
and an example of how you could create the VB code to call it. (Place code below in button click event)
vb Code:
' Create the connection
Dim lconn As New SqlConnection("Connection in here")
' Create the command
Dim lcommand As New SqlCommand
' Create the datareader
Dim lsqlDR As SqlDataReader
Try
' If the connection is closed
If lconn.State = ConnectionState.Closed Then
' Open the connection
lconn.Open()
End If
' Set the command text to the name of the stored procedure
It is possible to take each of those UPDATE queries and instead make them sub-queries in a SELECT list. Each sub-query would basically be the exact same syntax as the UPDATE shows.
You should use SELECT TOP 1 in the sub-query in case a student has both RDG1 and RDG2.
No real need to build a temp table - although that certainly can be done.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Reply With Quote
Originally Posted by szlamany
