Populate one table from another

Corporal Agarn

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 ???):

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.

Wendelius

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 Agarn

Here is what I currently use:

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.

Jorgen Andersson

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:

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:

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:

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 Agarn

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 Agarn

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.

Jorgen Andersson

Glad to be of service.

Wrong is evil and must be defeated. - Jeff Ello

Rony89

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

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

Rony89

Very simple. you should try this query below

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

Corporal Agarn

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.