Populate one table from another - Database Discussion Boards

I have three tables in the school database: students,exams,subjects. my Problem is how i can take the take the subjects records and admission no from students and use them to create a table call marks at the click of a button. Note: the no of records are unpredictable.
Student Table contains fields: admno,name,form
exam contains fields: ExamName
subjects: SubjectName,SubjectID
The Marks table final result should be
admno,name,form,ExamName,subject1,subject2,...subjectn(Based on the no of subjects in the subject table)
. Am using vb.net with mysql. Kindly assist. it has given me headache for weeks

KipkoechE wrote:
create a table call marks at the click of a button

I hope you mean inserting records into a table, not creating a table.

i mean creating a table based on the records in another table

Not a good idea.

Creating a dynamic table just means you'll have to write code to generate the CREATE TABLE statement and you'll have to do your checking to see how many columns should be included in it.

However, why would you want to create a new table? The design sounds like a bad idea.

There are only 10 types of people in the world, those who understand binary and those who don't.

The teacher just wants to check off the "Create Table AS SELECT" from his list of thing they should know about.

Wrong is evil and must be defeated. - Jeff Ello

If you can write a query to select the results, then it should be easy to get them in a new table. Right now, there are a few fields lacking - how would you know which student took which exam?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Hello, SQL Server 2008R2
I have a list of accounts I want to split between a set of agents. The account table and the agent table have no common value. So I use a while loop to do the populating. As a learning tool I am trying to find a way to update the table with only a query. Here is example code but I am missing the control (see ???):

SQL

CREATE TABLE #MyTestBase (IDX INT, ExampleID INT, TestID INT);
INSERT INTO #MyTestBase (IDX, ExampleID, TestID) VALUES
(132326,406,NULL),(132358,513,NULL),(132367,513,NULL),(132373,513,NULL),(132374,406,NULL),
(132379,406,NULL),(132381,513,NULL),(132405,369,NULL),(132416,406,NULL),(132433,369,NULL),
(132438,369,NULL),(132444,406,NULL),(132457,369,NULL),(132461,406,NULL),(132465,369,NULL),
(132481,369,NULL),(132488,406,NULL),(132494,406,NULL),(132499,513,NULL),(132500,406,NULL),
(132507,406,NULL),(132520,513,NULL),(132521,369,NULL),(132525,369,NULL),(132533,406,NULL),
(132537,369,NULL),(132545,406,NULL),(132551,513,NULL),(132556,513,NULL),(132572,406,NULL),
(132580,369,NULL),(132593,329,NULL),(132600,513,NULL),(132629,513,NULL),(132695,513,NULL),
(132711,406,NULL),(132730,329,NULL),(132781,513,NULL),(132782,369,NULL),(132787,513,NULL),
(132800,406,NULL),(132805,369,NULL),(132813,513,NULL),(132831,369,NULL),(132842,406,NULL),
(132863,513,NULL),(132876,329,NULL),(132880,329,NULL),(132889,329,NULL),(132891,329,NULL),
(132908,329,NULL),(132913,406,NULL);

CREATE TABLE #MyTestCounts (TestID INT, CNT INT);
INSERT INTO #MyTestCounts (TestID, CNT) VALUES
(329,7),
(369,13),
(406,17),
(513,15);

SELECT * FROM #MyTestCounts;
SELECT * FROM #MyTestBase;

UPDATE Base
SET TestID = Counts.TestID
FROM #MyTestBase Base
CROSS APPLY #MyTestCounts Counts
WHERE ??? <= Counts.CNT
AND TestID IS NULL;

In reality these are permanent tables with a lot more information and the update is more complicated but I will worry about that late.

Can someone point me in the right direction?

Thank you for your time,
djj

Mongo: Mongo only pawn... in game of life.

You said that you currently do the update using a while loop. Can you post that? It would help to understand what's the desired outcome.

Here is what I currently use:

SQL

DECLARE @TestID INT, @RemCount INT, @irow INT;

IF OBJECT_ID('tempdb..#UpdateAgent') IS NOT NULL DROP TABLE #UpdateAgent;
SELECT TestID, CNT AS RemCount
    ,  ROW_NUMBER() OVER(ORDER BY TestID) AS IDX
INTO #UpdateAgent
FROM #MyTestCounts;

SELECT @MIDX = MAX(IDX) FROM #UpdateAgent

WHILE @irow <= @MIDX
BEGIN
    SELECT @TestID = TestID, @RemCount = RemCount
    FROM #UpdateAgent
    WHERE IDX = @irow;

    IF @RemCount < 0 SET @RemCount = 0;

    UPDATE WQueueCheck
    SET TestID         = @TestID
    FROM #MyTestBase
    INNER JOIN (
            SELECT TOP (@RemCount) TQB.IDX
            FROM #MyTestBase TQB
            WHERE TestID IS NULL
            ORDER BY NEWID() ) X
        ON #MyTestBase.IDX = X.IDX;

    SET @irow += 1;
END

[edit] missed removing a column from production versus test

Mongo: Mongo only pawn... in game of life.

modified 6-Feb-15 13:37pm.

Tough one, it took me a while to understand what you wanted, and I still may have misunderstood the purpose.
Anyway, here's a try, tell me if I'm off the target.

Since there is no UnGroup or UnCount function in SQL we have to create that ourselves.
One way is to use an auxiliary Sequence. Here's a fairly fast way to create a sequence:

SQL

CREATE TABLE numbersequence (Number  int  not null);
INSERT INTO numbersequence(Number)
SELECT  TOP 100 row_number() over(order by t1.number) as N
FROM    master..spt_values t1 
CROSS JOIN master..spt_values t2
;

Adjust for the size you need.
Now we can "Uncount" the table and create a rownumber to join on:

SQL

select  testid
       ,ROW_NUMBER() OVER(order by testid) AS rn
from    MyTestCounts tc,numbersequence n
where   tc.cnt >= n.number

The whole query to connect unrelated IDX to TestID would look like this:

SQL

with counts as (
    select  testid
           ,ROW_NUMBER() OVER(order by testid) AS rn
    from    MyTestCounts tc,numbersequence n
    where   tc.cnt >= n.number
    )
,tqb as (
    SELECT  TOP (select sum(cnt) from MyTestCounts) TQB.IDX
           ,ROW_NUMBER() OVER(order by testid) AS rn
    FROM    MyTestBase TQB
    WHERE   TestID IS NULL
    ORDER BY NEWID()
    )
select  IDX,TestID
from    counts c
join    tqb t
    on  c.rn = t.rn

Here's[^] the fiddle.

Wrong is evil and must be defeated. - Jeff Ello

Thank you for the reply. I have been pulled off for the crisis of the day, but will get back to you when I can.

Mongo: Mongo only pawn... in game of life.

That does it.
Took me a minute to understand your logic, but it makes sense once I got it in my head (very thick you know).

Mongo: Mongo only pawn... in game of life.

Glad to be of service.

Wrong is evil and must be defeated. - Jeff Ello

Very simple.
You should try this my query.and dont use temp Table #MyTestCounts

SQL

UPDATE #MyTestBase
SET TestID = (SELECT COUNT(1) NumOfPoint
			  FROM #MyTestBase AS mtb
			  WHERE mtb.ExampleID = #MyTestBase.ExampleID
			  GROUP BY mtb.ExampleID)

The number of accounts an agent may have is allocated by a manager, thus the table #MyTestCounts.

Mongo: Mongo only pawn... in game of life.

Hi!
Im kinda out of my dept here. Im upgrading an old C++/CLI project to Visual Studio 2013, so I have a SQL Server Mobile database file (.sdf) that needs to be upgraded to current version (whatever that is). Im not sure what version it has now, but VS says I need to upgrade it with SqlCeEngine.Upgrade(). I´ve got SQL Server Pro 2014 installed on my computer, and when I tried to install Sql Server Compact 4.0 it said I have a later version installed already. Im totally lost here. Could someone please push me in the right direction.

modified 6-Feb-15 14:55pm.

In the end I solved it myself. It turned out the SQL Server Mobile file (.sdf) was created by version 3.5, while I tried to access it from Visual Studio 2013 with version 4.0. I solved it entering References under project properties. Then removing the reference to System.Data.SqlServerCe.dll (v4.0). After that I added a new reference to the System.Data.SqlServerCe.dll v3.5. I had to Browse my hard drive in order to find it. Problem solved.

A small note after some further research:

There is an add-in to Visual Studio called "SQL Server Compact/SQLite Toolbox" which give you som really nice tools for working with those databases from within Visual Studio. One of those features is to update the SQL Server CE database from v.3.5 to v.4.0.

Full Text Search sqlserver

Documentation[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Hi I have a huge database and my drive is full.
I have compressed ndf files with page .
Then I Shrink The file The Result Is About 215MB But ndf fileSize in filesysytem is 1.5 GB

please help me

Help you with what, you have not asked a question?

I just installed Oracle 11g Express. I then installed Oracle Developer. When I try to make a new connection, it wants a User Name and Password.

What User Name and Password is it expecting??? I didn't create a User Name!!

[UPDATE]
It was looking for 'system' as a user name. Google is your friend.

If it's not broken, fix it until it is

how can i transfer data from sql server 2008 to oracle using script