Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed? 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
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;