Insert into table variable with union

问题:

I've got a table variable that I'm wanting to insert a union query. The union query runs fine, but I can't seem to get the insert to work (syntax error)

INSERT INTO @table

(a,
b,
c,
d)

VALUES

(SELECT
       a,
       b,
       c,
       d 
FROM table1

UNION

SELECT
       a,
       b,
       c,
       d 
FROM table2)

Should this be working? I can post my real code if there is an issue elsewhere!

I'm getting a syntax error on the first SELECT

回答1:

INSERT INTO @table(a,b,c,d)
SELECT  a,b,c,d 
FROM   table1

UNION

SELECT a,b,c,d 
FROM table2

You do not need to use the Values clause when Inserting data using SELECT statement. Therefore I have removed the VALUES bit from it and just simply doing a UNION of rows being returned from both SELECT queries.

Sql server supports the syntax for INSERT statement like

INSERT INTO Table_Name(Col1, COl2. Col3...)
SELECT Col1, COl2. Col3...
FROM Other_Table_Name

This will insert that result set returned by the select statement into target table. In your case the Result is a UNION of two selects therefore it is not any different from a single select.

回答2:

"VALUES" isn't needed in this case

INSERT INTO @table (a, b, c, d)
SELECT a, b, c, d FROM table1 
UNION 
SELECT a, b, c, d FROM table2