SELECT * INTO Using a Select Statement

Question

Post reply

SELECT * INTO Using a Select Statement

AMCiti

SSC-Addicted

Points: 496
More actions
February 8, 2021 at 6:32 pm
Go to Answer
#3842587

Dear Group:
I am in the process of converting code that uses an OPENQUERY to data that now resides on our server, but in a different database.
We were using the following code:
SELECT * INTO [dbo].[temp_Weekly_Event_Report] FROM OPENQUERY(Oracle_PROD, ' SELECT DISTINCT column1 FROM table WHERE Event_Source = 1')
All this worked, but now I am trying to do the same thing in T-SQL, but it isn't letting me.
SELECT * INTO temp_Weekly_Event_Report FROM
SELECT
DISTINCT column1 FROM [SQLDatabase].[dbo].[Table] WHERE Event_Source = 1
I keep getting an "Incorrect syntax new the keyword 'SELECT'. I have tried to Google this, but cannot find a website that shows me how to do a SELECT * INTO using a SELECT statement.
Would appreciate any help.
ScottPletcher

SSC Guru

Points: 101098
More actions
February 8, 2021 at 6:46 pm
Answer
#3842598
You need parentheses to "tell" SQL you're using a subquery in the SELECT:
```
SELECT * INTO temp_Weekly_Event_Report FROM
(
SELECT

DISTINCT column1 FROM [SQLDatabase].[dbo].[Table] WHERE Event_Source = 1
) AS any_name
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2021 at 12:44 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AMCiti SSC-Addicted Points: 496 More actions · Answer 1

I tried it with the "Parentheses", but didn't have the "AS any_name" portion. Greatly appreciate the help 🙂

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You're welcome. Yeah, SQL requires the alias name (the "AS" is not required, I just like to use it but the "any_name" part is required).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

You can simplify those queries without using a sub-query.

SELECT DISTINCT 
       column1 
  INTO temp_Weekly_Event_Report
  FROM [SQLDatabase].[dbo].[Table] 
 WHERE Event_Source = 1

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs