CODEWITHSUNDEEP
sql-server
k Koduru
k Koduru

Reputation: 19

how to create table form the result set of another query

I am trying to create a table from the result set of the SQL query but I got syntax error at select and I struck at that point

these are ways which I tried 

create table schedulewidgetfix
 as
 (select * from PRSNLOCPROFMM

where PERSONALLOCATNPROFID in

(select PERSONALLOCATNPROFID from PERSONALLOCATNPROF where PERSONALLOCATNPROFID

not in (select PERSONALLOCATNPROFID from PRSNLOCPROFORGMM)))

and the other way I tried is 

SELECT *
INTO schedulewidgetfix
FROM

  (select * from PRSNLOCPROFMM

where PERSONALLOCATNPROFID in

(select PERSONALLOCATNPROFID from PERSONALLOCATNPROF where PERSONALLOCATNPROFID

not in (select PERSONALLOCATNPROFID from PRSNLOCPROFORGMM)))

here I got syntax error at the closing bracket so what is the way to create the table

Upvotes: 0

Views: 60

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You were close. You could either add an alias to the subquery like this.

SELECT * --you should list the columns here though instead of using *
INTO schedulewidgetfix
FROM

  (select * from PRSNLOCPROFMM

where PERSONALLOCATNPROFID in

(select PERSONALLOCATNPROFID from PERSONALLOCATNPROF where PERSONALLOCATNPROFID

not in (select PERSONALLOCATNPROFID from PRSNLOCPROFORGMM))) x

Or a cleaner approach would be to eliminate that subquery entirely like this.

SELECT * --you should list the columns here though instead of using *
INTO schedulewidgetfix
FROM PRSNLOCPROFMM
where PERSONALLOCATNPROFID in
(
    select PERSONALLOCATNPROFID 
    from PERSONALLOCATNPROF 
    where PERSONALLOCATNPROFID not in (select PERSONALLOCATNPROFID from PRSNLOCPROFORGMM)
)

Upvotes: 1

Related Questions