Easy Q.. Query without temp table

How do I rewrite this query so that I do not use a temp table?

SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

Use a derived table:

SELECT *

FROM (SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID ) as TEMP1
WHERE MINR1 = MAXR1

|||Thanks!sql

Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

dynamically creating temp tables

I have a dynamic sql which uses "select into" to create a temp table. It has
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )

dynamically creating temp table names

Hello,
I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000.

For example:

DECLARE @.l_personsUID int

select @.l_personsUID = 9842

create table ##Test1table /*then the @.l_personsUID */
(
resultset1 int

)

The key to the problem is that I want to use the variable
@.l_personsUID to name then temp table. The name of the temp table
should be ##Test1table9842 not ##Test1table.

Thanks for you help.

Billy"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.

May I ask why?

You can probably do this by dynamically building the string.

But it's going to be messy.

> Thanks for you help.
> Billy|||billy_cormic@.hotmail.com (Billy Cormic) wrote in message news:<dd2f7565.0311251937.cf18cf9@.posting.google.com>...
> Hello,
> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000.
> For example:
> DECLARE @.l_personsUID int
> select @.l_personsUID = 9842
> create table ##Test1table /*then the @.l_personsUID */
> (
> resultset1 int
>
> )
> The key to the problem is that I want to use the variable
> @.l_personsUID to name then temp table. The name of the temp table
> should be ##Test1table9842 not ##Test1table.
> Thanks for you help.
> Billy

You could use dynamic SQL, but that would not be a good solution. If
the table names are dynamic, then all code accessing the tables would
need to be dynamic also, and that will create a lot of issues.

A better approach would be to have a single, permanent table, with
personsUID as part of the key. See here for a good discussion of this
issue:

http://www.algonet.se/~sommar/dynam...html#Sales_yymm

Simon|||I want to do this so that i can create individual tables to set as
datasources for certain crystal reports.

"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<2jWwb.144035$ji3.17559@.twister.nyroc.rr.com>...
> "Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
> news:dd2f7565.0311251937.cf18cf9@.posting.google.co m...
> > Hello,
> > I am interested in dynamically creating temp tables using a
> > variable in MS SQL Server 2000.
> > For example:
> > DECLARE @.l_personsUID int
> > select @.l_personsUID = 9842
> > create table ##Test1table /*then the @.l_personsUID */
> > (
> > resultset1 int
> > )
> > The key to the problem is that I want to use the variable
> > @.l_personsUID to name then temp table. The name of the temp table
> > should be ##Test1table9842 not ##Test1table.
> May I ask why?
> You can probably do this by dynamically building the string.
> But it's going to be messy.
>
> > Thanks for you help.
> > Billy|||>> I am interested in dynamically creating temp tables using a
variable in MS SQL Server 2000. <<

Learn to write correct SQL instead. The use of temp tables is usually
a sign of really bad code -- the temp tables are almost always used to
hold steps in a procedural solution instead of a having a set-oriented
non-proceudral solution. This also says that you have no data model
and that any user, present or future, can change it on the fly.

Oh, if you don't care about performance, portability, readability,
security, and all that other stuff, then you can use dynamic SQL to
screw up your application this way.|||OK. I will just create anohter table... not a bunch of temp tables to
hold the results.

thanks

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311261052.12098cb6@.posting.google.com>...
> >> I am interested in dynamically creating temp tables using a
> variable in MS SQL Server 2000. <<
> Learn to write correct SQL instead. The use of temp tables is usually
> a sign of really bad code -- the temp tables are almost always used to
> hold steps in a procedural solution instead of a having a set-oriented
> non-proceudral solution. This also says that you have no data model
> and that any user, present or future, can change it on the fly.
> Oh, if you don't care about performance, portability, readability,
> security, and all that other stuff, then you can use dynamic SQL to
> screw up your application this way.

dynamic tablename issue

Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:

declare c cursor
for
select * from @.tablename
open c
fetch c into ...

but I get an error for declare cursor for dynamic tablename,

did sql server has some BIF to evaluate the variable for table name?

thanks for your kindly assistance.

regards,

Stanley Huanguse the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL|||Originally posted by yoavmaimon
use the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL
do you mean I can write the stored procedure as:

create proc sp_dynamicTableName
(
declare @.tablename varchar(20)
)
as
declare c cursor
for
exec('select * from ' & @.tablename)
open c
fetch c into ...

regards for your reply

thanks

Stanley Huang|||what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?|||Originally posted by yoavmaimon
what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?

Dear Sir:
Why cursor?
Because I have to parse the records and and by the columns to do some extra action.
After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
Do you know how to set the priority of specified account in sql server,
then I can avoid to use cursor.

regards,

Stanley Huang