TSQL - Mapping one table to another without using cursor

I have tables with following structure

create table Doc(
    id                  int identity(1, 1) primary key,
    DocumentStartValue  varchar(100)
)
create Metadata (
    DocumentValue       varchar(100),
    StartDesignation    char(1),
    PageNumber          int
)
GO

Doc contains
    id      DocumentStartValue
    1000    ID-1
    1100    ID-5
    2000    ID-8
    3000    ID-9

Metadata contains
    Documentvalue   StartDesignation    PageNumber
    ID-1            D                   0
    ID-2            NULL                1
    ID-3            NULL                2
    ID-4            NULL                3
    ID-5            D                   0
    ID-6            NULL                1
    ID-7            NULL                2
    ID-8            D                   0
    ID-9            D                   0

What I need to is to map Metadata.DocumentValues to Doc.id

So the result I need is something like

id      DocumentValue   PageNumber
1000    ID-1            0
1000    ID-2            1
1000    ID-3            2
1000    ID-4            3
1100    ID-5            0
1100    ID-6            1
1100    ID-7            2
2000    ID-8            0
3000    ID-9            0开发者_开发技巧

Can it be achieved without the use of cursor?

Something like, sorry can't test

;WITH RowList AS
(   --assign RowNums to each row...
    SELECT
        ROW_NUMBER() OVER (ORDER BY id) AS RowNum,
        id, DocumentStartValue
    FROM
        doc
), RowPairs AS
(   --this allows us to pair a row with the previous rows to create ranges
    SELECT 
       R.DocumentStartValue AS Start, R.id,
       R1.DocumentStartValue AS End
    FROM
       RowList R JOIN RowList R1 ON R.RowNum + 1 = R1.RowNum
)
--use ranges to join back and get the data
SELECT
    RP.id, M.DocumentValue, M.PageNumber
FROM
    RowPairs RP
    JOIN
    Metadata M ON RP.Start <= M.DocumentValue AND M.DocumentValue < RP.End

Edit: This assumes that you can rely on the ID-x values matching and being ascending. If so, StartDesignation is superfluous/redundant and may conflict with the Doc table DocumentStartValue

with rm as
(
  select DocumentValue
    ,PageNumber
    ,case when StartDesignation = 'D' then 1 else 0 end as IsStart
    ,row_number() over (order by DocumentValue) as RowNumber
  from Metadata
)
,gm as
(
  select
     DocumentValue as DocumentGroup
    ,DocumentValue
    ,PageNumber
    ,RowNumber
  from rm
  where RowNumber = 1

  union all

  select
     case when rm.IsStart = 1 then rm.DocumentValue else gm.DocumentGroup end
    ,rm.DocumentValue
    ,rm.PageNumber
    ,rm.RowNumber
  from gm
  inner join rm on rm.RowNumber = (gm.RowNumber + 1)
)
select d.id, gm.DocumentValue, gm.PageNumber
from Doc d
inner join gm on d.DocumentStartValue = gm.DocumentGroup

Try to use query above (maybe you will need to add option (maxrecursion ...) also) and add index on DocumentValue for Metadata table. Also, it it's possible - it will be better to save appropriate group on Metadat rows inserting.

UPD: I've tested it and fixed errors in my query, not it works and give result as in initial question.

UPD2: And recommended indexes:

create clustered index IX_Metadata on Metadata (DocumentValue)
create nonclustered index IX_Doc_StartValue on Doc (DocumentStartValue)