I've been trying to combine two tables into a temp table. My goal is to have the data of both tables available in one temp table

I tried a couple of things: 1.

SELECT * INTO #temp FROM Customers.Emails
UNION ALL
SELECT * INTO #temp FROM Customers.Location

SELECT *
INTO #temp
(Select
    All column names here etc
FROM Customer.Emails
UNION
SELECT
    All column names here etc
FROM Customer.Location)

When I tried 2 I got this error

Msg 263, Level 16, State 1, Line 1 Must specify table to select from. Msg 1038, Level 15, State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

CodePudding user response：

Here are 3 methods to do the INSERT INTO #temp. Method 1 requires both tables to have the exact same column names and count. The other 2 methods require you to define the columns you want inserted since we aren't using SELECT * anymore.

CREATE TABLE emails (
  email_address nvarchar(50)
);
  
CREATE TABLE locations (
  email_address nvarchar(50)
);
 
INSERT INTO emails VALUES ('[email protected]');
INSERT INTO locations VALUES ('[email protected]');

--Method 1
SELECT * INTO #temp FROM emails
UNION ALL
SELECT * FROM locations

SELECT *
FROM #temp;

--Method 2
SELECT *
INTO #temp2
FROM (
  SELECT
    email_address
  FROM emails
  UNION ALL
  SELECT
    email_address
  FROM locations
) as tbl

SELECT *
FROM #temp2;

--Method 3
WITH prelim AS (
  SELECT
      email_address
    FROM emails
    UNION ALL
    SELECT
        email_address
    FROM locations
)
SELECT *
INTO #temp3
FROM prelim;

SELECT *
FROM #temp3;

Method 1 Results:

Method 2 Results:

Method 3 Results:

fiddle