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:
email_address |
---|
[email protected] |
[email protected] |
Method 2 Results:
email_address |
---|
[email protected] |
[email protected] |
Method 3 Results:
email_address |
---|
[email protected] |
[email protected] |