SQL Server: Select from two tables and insert into

问题:

I have a stored procedure with two table variables (@temp and @temp2).

How can I select the values from both temp tables (Both table variables contain one row) and insert them all in one table ?

I tried the following but this didn't work and I got the error that the number of SELECT and INSERT statements does not match.

DECLARE @temp AS TABLE
    (
        colA datetime,
        colB nvarchar(1000),
        colC varchar(50)
    )
DECLARE @temp2 AS TABLE
    (
        colD int
    )

...

INSERT INTO MyTable
    (
        col1,
        col2,
        col3,
        col4
    )
SELECT  colD FROM @temp2,
        colA FROM @temp,
        colB FROM @temp,
        colC FROM @temp

Many thanks for any help with this, Tim.

回答1:

As both table variables have a single row you can cross join them.

INSERT INTO MyTable
            (col1,
             col2,
             col3,
             col4)
SELECT t.colA,
       t.colB,
       t.colC,
       t2.colD
FROM   @temp t
       CROSS JOIN @temp2 t2 

回答2:

you should use this if you have only single row in both table @temp and @temp2, because this is a cartesian product.

INSERT INTO MyTable(col1,col2,col3,col4)
  SELECT t.colA,
           t.colB,
           t.colC,
           t2.colD
    FROM   @temp t,@temp2 t2