CODEWITHSUNDEEP
sqlsql-servertemp-tables
Yash
Yash

Reputation: 13

Converting SQL query result to temp table

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))
    )

Upvotes: 1

Views: 1595

Answers (2)

shambhu yadav
shambhu yadav

Reputation: 251

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))

Upvotes: 1

Thom A
Thom A

Reputation: 95989

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);

Upvotes: 2

Related Questions