Converting SQL query result to temp table

Question

I am trying to turn my sql results into a temp table but it keeps on returning the following error:

Expecting As or Id or quoted-ID.

I know the sql result is correct but when I add SELECT * INTO #newtable FROM, the sql query doesn't work. What is wrong?

SELECT *
INTO #newtable
FROM 
    (
    SELECT
        Url,Id 
    FROM Blob 
    WHERE 
        Id IN 
            (
            SELECT 
                BlobId 
            FROM 
                XrefBlobProjectMeeting
            ) 
        AND Extension NOT IN ('xlsx','xls','avi','jpg','mp4','wmv','png')
        AND (RefContentTypeId IN (11,13,14,35))
    )

@RaymondNijland INSERT INTO ... SELECT and SELECT...INTO behave very differently. — Thom A
– Thom A ♦, Commented Jul 13, 2018 at 14:22
@RaymondNijland it doesn't. it says it is "basically the same". I actually complete disagree with that. For SyBase (SQL Server now) if you were to run INSERT INTO dbo.MyTable SELECT 1 AS i; The statement will fail, however, SELECT 1 AS i INTO dbo.MyTable; will work fine. Conversely CREATE TABLE MyTable (i int); INSERT INTO dbo.MyTable SELECT 1 AS i; will work fine, but CREATE TABLE MyTable (i int); SELECT 1 AS i INTO dbo.MyTable; will not. — Thom A
– Thom A ♦, Commented Jul 13, 2018 at 14:27
The SELECT..INTO syntax will create the object as part of the statement. if the object already exists, the statement fails. for INSERT INTO...SELECT the complete opposite is true; the object must already exist, and if it does not, the statement will fail. — Thom A
– Thom A ♦, Commented Jul 13, 2018 at 14:29

Thom A · Accepted Answer · 2018-07-13 14:18:49Z

2

Not sure why you have the nested SELECTs. This is what you're more likely after, as you then don't have to alias your subquery:

SELECT Url,
       Id
INTO #newtable
FROM Blob
WHERE Id IN (SELECT BlobId FROM XrefBlobProjectMeeting)
  AND Extension NOT IN ('xlsx', 'xls', 'avi', 'jpg', 'mp4', 'wmv', 'png')
  AND (RefContentTypeId IN (11, 13, 14, 35));

You'd be ever better off, however, changing the IN to an EXISTS as well though:

SELECT [Url],
       Id
INTO #newtable
FROM Blob B
WHERE EXISTS (SELECT 1
              FROM XrefBlobProjectMeeting E
              WHERE E.BlobID = B.ID)
  AND Extension NOT IN ('xlsx', 'xls', 'avi', 'jpg', 'mp4', 'wmv', 'png')
  AND RefContentTypeId IN (11, 13, 14, 35);

answered Jul 13, 2018 at 14:18

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thom A Over a year ago

Answering your comment on your question, @Yash , as it's in relation to the answer. This would create the temporary table #newtable. Any table with a name beginning with # is a temporary table, and those with a single # will only persist for the duration of the connection that created it.

Yash Over a year ago

Thank you that makes sense.

shambhu yadav · Accepted Answer · 2018-07-13 15:40:59Z

1

I have tried your problem in your way only and it is working for me. Solution is like given below;

SELECT Url,Id
INTO #newtable
    FROM Blob 
    WHERE 
        Id IN 
            (
            SELECT 
                BlobId 
            FROM 
                XrefBlobProjectMeeting
            ) 
        AND Extension NOT IN ('xlsx','xls','avi','jpg','mp4','wmv','png')
        AND (RefContentTypeId IN (11,13,14,35))

answered Jul 13, 2018 at 15:40

shambhu yadav

2512 silver badges13 bronze badges

Collectives™ on Stack Overflow

Converting SQL query result to temp table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related