SELECT * INTO Using a Select Statement
-
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.
-
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_nameSQL 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".
-
I tried it with the "Parentheses", but didn't have the "AS any_name" portion. Greatly appreciate the help
-
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".
-
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 = 1Jeffrey 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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply