Post reply

SELECT * INTO Using a Select Statement

  • 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.

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • February 8, 2021 at 6:56 pm

    #3842601

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

  • February 8, 2021 at 6:58 pm

    #3842602

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • February 8, 2021 at 8:25 pm

    #3842638

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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