Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed?

Question

Sorry for the confusing title, I wasn't sure what to write there.

I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned.

For example, if TableA is

Row_Number()    Id
1               1
2               2
3               3
4               4
5               5
6               6
7               7
8               8
9               9
10              10

and TableB is

Row_Number()    Id
1               1
2               2
3               3

I need an end result set that is

UserId          RecordId
1               1
2               2
3               3
1               4
2               5
3               6
1               7
2               8
3               9
1               10

I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable.

The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF.

-- Converts a comma-delimited string into a table
SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
INTO #tmpTest
FROM dbo.StringToNumSet('2,3,1', ',') 

DECLARE @test int
SELECT @test = Count(*) FROM #tmpTest

SELECT *
FROM #tmpTest as T1
INNER JOIN (
    SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
    FROM TableA WITH (NOLOCK)
) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1

Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3.

Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table

Is there a way of joining these tables in a single query so I won't need to use a temp table and variable?

I'm using SQL Server 2005

Answer 1

Another way to avoid temp tables would be this:

;WITH tmpTest AS
(
    SELECT  Num as [UserId]
            , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
            , COUNT(*) OVER() AS Quant
    FROM dbo.StringToNumSet('2,3,1', ',') 
)
SELECT *
FROM tmpTest as T1
INNER JOIN 
    (
        SELECT Top 10 Id
            , Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
        FROM TableA WITH (NOLOCK)
    ) as T2 ON T1.RowNo = (T2.RowNo % Quant) + 1;

Answer 2

Yes, you can do this without any temp tables:

with w as (select usr_id, row_number() over (order by usr_id) as usr_ordinal from usr)
select record_id, ( select usr_id
                    from w
                    where usr_ordinal=1+(record_ordinal%(select count(*) from w))
                  ) as usr_id
from ( select record_id, row_number() over (order by record_id) as record_ordinal 
       from record ) as z;

See here for a SQLFiddle demo