x

execute SQL problem

use adventureworks  

go

declare @sql varchar(100)
select @sql=
'select * into #temptbl from HumanResources.Employee'

execute (@sql)

when i run

select * from #temptbl

i got error msg. what is wrong

more ▼

asked Feb 03 '12 at 04:29 AM in Default

binodbabu gravatar image

binodbabu
290 7 10 12

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The problem is the scope of the query. Dynamic queries have their own scope and after its execution the #temptable would be dropped. To see this happening use this script

DECLARE @sql VARCHAR(200)
SELECT  @sql = 'select * into #temptbl from Employee 
select * from #temptbl
select * from [tempdb].sys.[tables] where name like ''#temptbl%'''

EXECUTE (@sql)

SELECT  *
FROM    [tempdb].sys.[tables]
WHERE   [name] LIKE '#temptbl%'

You would be able to fetch the records, and you would see the existence of the #temptbl table. But after the execution the table would not exist anymore.

So the solutions could be to use the SELECT statement inside the dynamic query OR use a GLOBAL temporary table.

But first thing is why you need DYNAMIC query for this? Please let us know then we might be able to lead you to a better approach.
more ▼

answered Feb 03 '12 at 04:51 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

@Usman, Is right Dynamic queries have their own scope and after its execution the #temp table would be dropped. So you need to check whether you can achieve your requirement without dynamic query or not other wise you will have to put your select statement inside the Dynamic query as @Usamn explains in his example.

Another way you can use as given in below example-

CREATE TABLE EMP(ID INT,NAME VARCHAR(100))

INSERT INTO EMP VALUES(1,'SQL')

CREATE TABLE #EMP(ID INT,NAME VARCHAR(100))

DECLARE @sql VARCHAR(200)

SELECT @sql = 'INSERT INTO #EMP(ID,NAME) select * from EMP'

EXECUTE (@sql)

SELECT * FROM #EMP

DROP TABLE #EMP

DROP TABLE EMP

more ▼

answered Feb 03 '12 at 05:14 AM

Amardeep gravatar image

Amardeep
1.3k 84 88 89

(comments are locked)
10|1200 characters needed characters left

I have query like this and i want the record of this query in seperate temp table. can you please help me now?

DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) declare @sold_to_id varchar(20)='10259300' SELECT @listCol = STUFF(( SELECT DISTINCT '],[' + field_name FROM VW_SMART_TRACKER_DATA where sold_to_id=@sold_to_id ORDER BY '],[' + field_name FOR XML PATH('') ), 1, 2, '') + ']' SET @query = ' SELECT * INTO #TEMPTBL FROM (SELECT sold_to_id, stuid, web_grp_id, field_name, value FROM VW_SMART_TRACKER_DATA WHERE sold_to_id = ' + @sold_to_id + ' and field_name is not null ) src PIVOT (max(value) FOR Field_name IN (' +@listCol+')) AS pvt select * from #temptbl'

EXECUTE (@query)
more ▼

answered Feb 03 '12 at 03:34 PM

binodbabu gravatar image

binodbabu
290 7 10 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977

asked: Feb 03 '12 at 04:29 AM

Seen: 737 times

Last Updated: Feb 03 '12 at 03:34 PM