Post reply

Select INTO using Query

  • August 26, 2003 at 9:55 am

    #81029

    Hi,

    I'm not sure if this is possible, but what I would like to do is create a temporary table based on the results of a query. I may just be getting mixed up with the syntax, I'm sure I have done it before.

    This works and puts all the values from the tbl_test table into the #temp table

    SELECT *

    INTO #temp

    FROM TBL_test

    what I need to do is replace the table with a select subquery

    ie

    SELECT *

    INTO #temp

    FROM (Select * from TBL_test)

    but this doesn't work.

    If I knew the field names I could use Insert INTO, but I don't know in advance what fields will be returned in the live query.

    Thanks in Advance

    Chris

  • August 26, 2003 at 10:03 am

    #471454

    Try something like:

    SELECT * INTO #temp

    from Select * from TBL_test

    or

    SELECT * INTO #temp2

    from Select * from TBL_test where col1 = 'abc'

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • August 27, 2003 at 12:27 am

    #471536

    When you say it doesn't work, what exactly do you mean? Syntax error or no results returned?

    I would have just added a alias for the derived table used, e.g.

    SELECT *

    INTO #temp

    FROM (Select * from TBL_test) AS qry

    Nikki Pratt

    Development DBA


    Nikki Pratt
    Development DBA

  • August 27, 2003 at 1:00 am

    #471543

    Agree post the error?

  • August 27, 2003 at 1:08 am

    #471544

    Provide an alias to this table...

    SELECT *

    INTO #temp

    FROM (Select * from TBL_test) ttttt

    Cheers..

    Prakash Heda

    Sr Consultant DBA

    Bangalore, India

    Chat: hedafriends@yahoo.com

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • August 27, 2003 at 5:49 am

    #471574

    Thanks Guys,

    Adding the alias solved the problem.

    Cheers

    Chris

  • August 27, 2003 at 10:13 am

    #471640

    Try this:

    SELECT * INTO #temp2

    from TBL_test where col1 = 'abc'

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

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