473,900 Members | 3,343 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating a common table expression--temporary table--using TSQL???

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!

Jul 23 '05 #1
11 16305
On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:
Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!


I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.
Jul 23 '05 #2
You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul

Jul 23 '05 #3
Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

On 28 Dec 2004 07:19:13 -0800, "Paul" <st*******@yaho o.com> wrote:
You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.


Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2
You can just about anything with a temp table that you can with a
normal table, including indexes.
HTH
Paul


Jul 23 '05 #4
On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:
Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!


Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #5
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:eu******** *************** *********@4ax.c om...
On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:
Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!


Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability , and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.

--
JAG
Jul 23 '05 #6
Hi John,

Thanks for your explanation. I had heard that WITH would be introduced in
SQL Server 2005; unfortunately, I'll have to wait a little longer before
I'll get a chance to actually play with it. (I don't have a spare system
lying around that I can use to safely toy with beta software).

It does look promising, though. I'm sure I'll really get to like this
feature once I have it available!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #7
On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:eu******* *************** **********@4ax. com...
On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:
>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!


Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability , and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.


Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?
Jul 23 '05 #8
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:h8******** *************** *********@4ax.c om...
On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:eu******* *************** **********@4ax. com...
On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability , and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.


Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?


No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.

--
JAG
Jul 23 '05 #9
On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <ja*@acm.org> wrote:
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:h8******* *************** **********@4ax. com...
On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <ja*@acm.org> wrote:
>"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
>news:eu******* *************** **********@4ax. com...
>> On 28 Dec 2004 07:07:49 -0800, ra***********@i ns.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> Hi Randi,
>>
>> I don't know if it's exactly the same as the DB2 version (probably not),
>> but SQL Server supports derived table expressions. Example (from BOL):
>>
>> USE pubs
>> GO
>> SELECT ST.stor_id, ST.stor_name
>> FROM stores AS ST,
>> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> FROM sales
>> GROUP BY stor_id
>> ) AS SA
>> WHERE ST.stor_id = SA.stor_id
>> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>>
>> Best, Hugo
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>Hi Hugo, a common table expression, provided by the WITH
>clause, is defined in Standard SQL (beginning with SQL:1999)
>and is implemented in SQL Server 2005. Semantically, the WITH
>clause is similar to defining one or more views whose scope and
>extent is the enclosed query. Factoring out and naming these
>common subexpressions in a query is meant to aid readability,
>conciseness, maintainability , and even efficiency. There are cases
>when a derived table is a perfectly good alternative, however,
>when that derived table is used multiple times in the query a
>common table expression becomes handy.
>
>Taking the BOL example from above, imagine you wanted to
>rank stores in decreasing order by number of distinct titles. Using
>WITH, one could write (admittedly, in this case a view is a reasonable
>choice too):
>
>WITH DistinctTitles (stor_id, title_count) AS
> (SELECT stor_id, COUNT(DISTINCT title_id)
> FROM sales
> GROUP BY stor_id)
>SELECT T1.stor_id, T1.title_count,
> COUNT(DISTINCT T2.title_count) AS stor_rank
>FROM DistinctTitles AS T1
> INNER JOIN
> DistinctTitles AS T2
> ON T2.title_count >= T1.title_count
>GROUP BY T1.stor_id, T1.title_count;
>
>It's also through the WITH clause that we can define recursive queries. This
>is where WITH truly shines.


Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?


No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.


Darn - I thought this would finally be a tool for removing SQL code
duplication within stored procedures. Does SQL Server 2005 offer some other
new feature to do this?
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5683
by: Galina | last post by:
Hello I am trying to create a table, which includes a field type LONG. The wizard hasn't allowed me to enter storage settings for this table. It set the initial size is 64K and not available to change, next size 0K, increase size by 0%. Since I am going to copy data from an existing MS Access table into this one, I know that the initial amount of records will be about 70000 and it will take about 7 Mb of disk space. I know it, because I...
2
2457
by: N. Shamsundar | last post by:
What is the SQL Server equivalent of DB2 common table expressions? For example, with gry(year,count) as( select floor(sem/10),count(distinct ssn) from grades group by floor(sem/10) ) select year,sum(count) Head_Count from gry group by year
0
2299
by: Jacob Pallapati | last post by:
Hi, I have a trigger in which I build a String from the contents of a table. In a stored procedure I would use a cursor which would look like: set str_to_build = ''; create c1 cursor for select col1 from table1;
1
5032
by: Endif | last post by:
I am tring to execute the following SQL statements through the Iseries Navigator for DB2/V8.2, But i come up with an error saying recursion is not allowed in common table expression. This is a example i picked up from SQL cook book. I am not sure where i am wrong. Any help is appreciated WITH TEMP ( SUPV_ID,EMPID, FIRSTNAME) AS ( SELECT TV.SUPV_ID,TV.EMPID, TV.FIRSTNAME
3
1923
by: Priya | last post by:
Hi, I need to create a temp table in my asp.net web services program. I have an xml file of the format <uom_masters> <uom_code>varchar</uom_code> <uom_description>varchar</uom_description> <uom_tolerance>money</uom_tolerance> </uom_masters>
3
9772
by: Frank Swarbrick | last post by:
I was just messing around trying to learn things and attempted the following: select brch_nbr , sum(case when post_flag != 'P' then amount else 0 end) as sum_amount from film.film_transactions group by brch_nbr having sum_amount 0 order by sum_amount desc ;
5
2536
by: KewlToyZ | last post by:
Good day, I am stuck in a strange situation. SQL 2000 Server, creating a stored procedure to use in Crystal Reports 11. I am trying to build a report without creating a table or temprorary table in the database. Not too bad but ugly nonetheless. I succedded in creating the process using multiple stored procedures, but the report takes hours to run in Crystal which more less indicates I failed to get a workable solution. I have a few...
3
2438
by: sqluser | last post by:
I have set of standard tables like Products, Customers, Orders, OrderDetails, and so on - but I need to run reports on a particular subset of the data or categorize the tables to make it one or few big tables to generate reports. I am wondering which approach is good, using Common Table Expressions or the stored procedure or if you have any good approach please suggest
1
1342
by: replica watch | last post by:
I am creating a table cell by cell (create the table, add columns, create row, add cells to the row) in which I want to add the capability for users to sort ASC and DESC by column header. Can anyone give me an idea or an example of how can I do this in code ? Thank you. Dan HTTP://www.21watch.com
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9671
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8043
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6082
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3320
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.