How to Insert combining two different selects in SQL Server?

I want to insert into a table as part of a longer procedure using SQL Server 2008. The table for the insert has two columns, itemID and tag.

The tag part is selected from an input parameter (xml) and works as intended with the line below. My problem is that the itemID needs to be selected from a temp table (@temp) at the same time. How can I achieve this here ?

My SQL (only relevant part):

INSERT  INTO RC_Items_Tags
(
        itemID,
        tag
)
SELECT 
        itemID,
        ParamValues.tag.value('.', 'varchar(255)')
FROM 
        @xmlTags.nodes('/tags/tag') AS ParamValues(tag)

Answer

Just perform a CROSS JOIN:

INSERT  INTO RC_Items_Tags
(
        itemID,
        tag
)
SELECT 
        t.itemID,
        ParamValues.tag.value('.', 'varchar(255)')
FROM 
        @xmlTags.nodes('/tags/tag') AS ParamValues(tag)
              CROSS JOIN
        @temp t

Advertisement

Answer