October 17, 2008 at 9:00 am
[font="Arial"][font="Arial Black"]Is it possible to Insert the values of a Dynamic query into a temp table.
Declare @StrQuery varchar(40000)
Set @StrQuery=''Select * from Employee'';
Exec(@StrQuery);
My Actual Query is ofcourse much complex.IS it Possible
to insert the result into a temp table like
Insert into #Temp(.. )
Exec(@StrQuery); [/font][/font]
October 17, 2008 at 9:07 am
Yes it is, provided that the temp table exists from the calling session (of the dynamic statement). You can also use
EXEC('INSERT INTO #Temp (columnlist) SELECT...')
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 17, 2008 at 9:15 am
Create Table #employee
(
Empid int,
Employee varchar(20)
)
Create Table #employee1
(
Empid int,
Employee varchar(20)
)
Insert into #Employee values('1','Sirish')
Insert into #Employee values('2','Sateesh')
Declare @Strquery varchar(100)
SET @StrQuery='Select Empid,Employee from #Employee'
EXEC('INSERT INTO #employee1 (Empid,Employee) SELECT ''+@Strquery+''')
Select * from #Employee1
Getting Some Errors.
October 17, 2008 at 9:21 am
Try this, then.
DROP TABLE #employee
DROP TABLE #employee1
Create Table #employee
(
Empid int,
Employee varchar(20)
)
Create Table #employee1
(
Empid int,
Employee varchar(20)
)
Insert into #Employee values('1','Sirish')
Insert into #Employee values('2','Sateesh')
Declare @Strquery varchar(100)
SET @StrQuery='Select Empid,Employee from #Employee'
EXEC(@StrQuery)
SET @StrQuery='INSERT INTO #employee1 (Empid,Employee) Select Empid, Employee from #Employee'
EXEC(@StrQuery)
Select * from #Employee1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 17, 2008 at 9:35 am
Thanx Chris.
But my Problem is slightly Complex.What will be stored in strquery
is a large dynamic query with many conditions.So Please suggest
a way to directly use StrQuery if at all it is possible.
Like the way I was trying in the previous reply.Once again many thanks.
Thanks and Regards,
Sirish
October 17, 2008 at 9:47 am
Simply append your query with the insert into query. Using the parameter itself, because you cannot execute an appened string unless it's in a single string. Here's an example:
DROP TABLE #employee
DROP TABLE #employee1
Create Table #employee
(
Empid int,
Employee varchar(20)
)
Create Table #employee1
(
Empid int,
Employee varchar(20)
)
Insert into #Employee values('1','Sirish')
Insert into #Employee values('2','Sateesh')
Declare @Strquery varchar(100)
SET @StrQuery='Select Empid,Employee from #Employee'
EXEC(@StrQuery)
SET @StrQuery='INSERT INTO #employee1 (Empid,Employee) ' + @StrQuery
SELECT @StrQuery
EXEC(@StrQuery)
Select * from #Employee1
Cheers,
Cheers,
J-F
October 17, 2008 at 9:54 am
Sirish, can you rewrite the stored procedure as a table valued function? Depending on what you are doing inside of it, it may not be possible, but if it is, you can do:
select *
into #temptable
from Function(@paramater)
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 17, 2008 at 10:01 am
Sirish (10/17/2008)
[font="Arial"][font="Arial Black"]Is it possible to Insert the values of a Dynamic query into a temp table.Declare @StrQuery varchar(40000)
Set @StrQuery=''Select * from Employee'';
Exec(@StrQuery);
My Actual Query is ofcourse much complex.IS it Possible
to insert the result into a temp table like
Insert into #Temp(.. )
Exec(@StrQuery); [/font][/font]
After reading INSERT in BOL, what you want to do should work. They even have an example in BOL where they use execute like that.
😎
October 17, 2008 at 10:04 am
Sirish (10/17/2008)
Create Table #employee(
Empid int,
Employee varchar(20)
)
Create Table #employee1
(
Empid int,
Employee varchar(20)
)
Insert into #Employee values('1','Sirish')
Insert into #Employee values('2','Sateesh')
Declare @Strquery varchar(100)
SET @StrQuery='Select Empid,Employee from #Employee'
EXEC('INSERT INTO #employee1 (Empid,Employee) SELECT ''+@Strquery+''')
Select * from #Employee1
Getting Some Errors.
You have few errors in the line that executes the dynamic SQL. You need to get rid of one of the select key words because you added it to the end of the insert part and to the beginning of the select part, so you are getting select select. Also you don’t need to use apostrophe when you add the variable that holds the dynamic select statement to the insert statement. In short instead of this line:
EXEC('INSERT INTO #employee1 (Empid,Employee) SELECT ''+@Strquery+''')
You should write this line:
EXEC('INSERT INTO #employee1 (Empid,Employee) '+@Strquery+'')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply