Database

KipkoechE8-Feb-15 18:56

KipkoechE

8-Feb-15 18:56

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

PIEBALDconsult9-Feb-15 3:47

PIEBALDconsult

9-Feb-15 3:47

Not a good idea.

ZurdoDev9-Feb-15 4:01

ZurdoDev

9-Feb-15 4:01

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.

Jörgen Andersson9-Feb-15 4:09

Jörgen Andersson

9-Feb-15 4:09

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

Populate one table from another

Eddy Vluggen9-Feb-15 5:53

Eddy Vluggen

9-Feb-15 5:53

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[^]

Corporal Agarn6-Feb-15 4:50

6-Feb-15 4:50

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.

Wendelius6-Feb-15 5:23

Wendelius

6-Feb-15 5:23

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.

Corporal Agarn6-Feb-15 6:49

6-Feb-15 6:49

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.

Jörgen Andersson9-Feb-15 21:29

Jörgen Andersson

9-Feb-15 21:29

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

Corporal Agarn10-Feb-15 0:42

10-Feb-15 0:42

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.

Corporal Agarn10-Feb-15 2:23

10-Feb-15 2:23

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.

Jörgen Andersson10-Feb-15 4:23

Jörgen Andersson

10-Feb-15 4:23

Glad to be of service.

Wrong is evil and must be defeated. - Jeff Ello

Rony8910-Feb-15 17:00

Rony89

10-Feb-15 17:00

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)

Corporal Agarn11-Feb-15 0:43