How to execute this SQL Query

Question

I am having following scenario

Drop Table #Temp
Create Table #Temp(name1 text, name2 text)

Insert INTO #Temp Values ('test','test')
Insert INTO #Temp Values ('test','test')

Insert Into #Temp1 Select * From 
(
;With CTE as (
 Select * from #Temp
 ) 
 select * from CTE
)

I know we can't use CTE as subquery .. but for hard time I don't know exact syntax of subquery since it is being provided by other system.

just image this

 Insert Into #Temp1 Select * From 
    (
     "Query Provided by Other System"
    )

So I don't have any control on subquery ("Query Provided by Other System").. And I have also tried dynamic sql query like

Declare @subquery nvarchar(max)

set @subquery=';With CTE2 as ( Select * from #Temp) select * from CTE2'

INSERT INTO #Temp1 From (EXEC sp_executesql @subquery)

This also gives error...

More Things to know:

i)I don't know about what are the columns will sub query returns

ii)And I don't have any control in sub query . like what is syntax of subquery and how it looks like?

so from these things, even I can't use dynamic sql (EXEC sp_executesql).because I don't know what will happen if @subquery itself contains dynamic sql.

Please help anyone...

You should not prefix the WITH with ;. The ; is there to define the end of a statement. The usual (bad) habit to prefix with with ; is only there to ensure the previous statement was terminated properly. — user330315
– user330315, Commented Apr 11, 2014 at 7:01
You might be able to use some variation of OPENQUERY technet.microsoft.com/en-us/library/… or OPENROWSET technet.microsoft.com/en-us/library/ms190312.aspx. — liebs19
– liebs19, Commented Apr 11, 2014 at 15:53

Ranadeera Kantirava · Accepted Answer · 2014-04-11 07:41:21Z

1

Use like this, with dynamic sql. It will work...

Insert Into #Temp1 
EXEC sp_executesql @subquery

edited Apr 11, 2014 at 7:41

answered Apr 11, 2014 at 7:27

Ranadeera Kantirava

3243 silver badges11 bronze badges

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

2 Comments

kombsh Over a year ago

yes but, I am getting this error Msg 208, Level 16, State 0, Line 12 Invalid object name '#Temp1'.

kombsh Over a year ago

I don't have pre created table #Temp1 .. need to create this dynamically

Eralper · Accepted Answer · 2014-04-11 07:14:51Z

0

You can use the below syntax

;With CTE as (
    Select * from #Temp
) 
Insert Into #Temp1 
Select * 
From (
    select * from CTE
) t

or if you don't have already created the #Temp1 table, use below code. It will automatically create the #temp1 table according to the selected fields

;With CTE as (
    Select * from #Temp
) 
Select * 
Into #Temp1 
From (
    select * from CTE
) t

Besides syntax, you can simplify above SQL code as follows

;With CTE as (
    Select * from #Temp
) 
Insert Into #Temp1 
Select * 
From CTE

or better

Insert Into #Temp1 
Select * from #Temp

answered Apr 11, 2014 at 7:14

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to execute this SQL Query

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