Displaying the output

Hi,
I want to write a query such that part of the output is displayed and the
complete output is put into a temp table.
For example i have a table customer and i want the total customer details to
be put in a temp table and the top 5 to be displayed.
select * into #temp1 from customer
select top5 * from customer
I run this two queries for my result, but is there any way to get the result
in one query.
Thanks,
MaheshMahesh wrote:
> Hi,
> I want to write a query such that part of the output is displayed
> and the complete output is put into a temp table.
> For example i have a table customer and i want the total customer
> details to be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result in one query.
> Thanks,
> Mahesh
No. You are talking about two different queries. You need to use an
ORDER BY with the TOP clause, unless you want somewhat random data
returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
My requirement is when i run a query it should go to temp table (e.g.
#temp1) and also display the output. This should happen on a single run of
query. Can you please help me in this.
Thanks,
Mahesh
"David Gugick" wrote:

> Mahesh wrote:
> No. You are talking about two different queries. You need to use an
> ORDER BY with the TOP clause, unless you want somewhat random data
> returned.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Mahesh
can you explain why you want to do that.
however it appears to be possible with functoon or procedure
--procedure
CREATE PROC DISPLAY
AS
select * into #temp1 from customer
select top5 * from customer ORDER BY CUSTOMER_ID
RETURN
GO
EXEC DISPLAY
GO
--function: you can't use temp table here but table variable
CREATE FUNCTION DISPLAYFUNCTION()
RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
AS
BEGIN
INSERT INTO @.TOPTABLE
SELECT * FROM EMPLOYEES
RETURN
END
GO
SELECT TOP 5 * FROM DISPLAYFUNCTION()
GO
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:

> Hi,
> I want to write a query such that part of the output is displayed and t
he
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the resu
lt
> in one query.
> Thanks,
> Mahesh|||space between top and 5 is missing; TOP 5
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||RD, this is requires because i will be running multiple queries to analyze
the data, while i am running i would like to see the data and then when at
the end if it is stored in a temp table then i can run a simple select on
that temp table instead of running the whole query again. can you please hel
p
me on this.
Thanks,
Mahesh
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||David already answered. One query cannot both store the results in a temp ta
ble *and* display the
results. It is one of those, but not both. How about pushing all data into t
he temp table and then
SELECT TOP 5 from there?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:E23898C7-8D15-492B-9CDE-0CFE24AC44CA@.microsoft.com...
> Hi David,
> My requirement is when i run a query it should go to temp table (e.g.
> #temp1) and also display the output. This should happen on a single run of
> query. Can you please help me in this.
> Thanks,
> Mahesh
> "David Gugick" wrote:
>|||Either you got to use sprocs or use two queries at a time with GO in betwee
n
--
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:
> RD, this is requires because i will be running multiple queries to analyze
> the data, while i am running i would like to see the data and then when at
> the end if it is stored in a temp table then i can run a simple select on
> that temp table instead of running the whole query again. can you please h
elp
> me on this.
> Thanks,
> Mahesh
> "R.D" wrote:
>|||Hi,
Try this. I did it with the northwind DB
select * into #temp1 from customers where customerid in (select top 5
customerid from customers)
Nils
"Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
> Hi,
> I want to write a query such that part of the output is displayed and
> the
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
> to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result
> in one query.
> Thanks,
> Mahesh|||nils
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
this inserts only five rows while Mahesh wants all rows to temp and five
rows to be displayed.
--
Regards
R.D
--Knowledge gets doubled when shared
"Nils Wolf" wrote:

> Hi,
> Try this. I did it with the northwind DB
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
> Nils
>
> "Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
>