combining select in one resultset 1

[ecannizzo]

If I do this:

select 'here' as col1

select 'here2' as col1

it outputs two different resultsets. Is there a way to make this output one resultset with two rows (instead of two resultsets with one row each)?

Thanks!

 

[SQLDenis]

select 'here' as col1
UNION ALL
select 'here2' as col1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ecannizzo]

Is there a way to do this from within a cursor? So each select is being called in a while loop within a cursor.

 

[SQLDenis]

Please tell me you just didn't say the evil word?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ecannizzo]

evil? Maybe, not sure. Let's say I have something like this (this is not exactly what I'm doing but for simplicity sake):

DECLARE MY_CURSOR Cursor
FOR
Select
D.ID
From
[dbo].[Dis] D


OPEN My_Cursor
DECLARE @ID

Fetch NEXT FROM MY_Cursor INTO @ID

--while there are records
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

--return necessary columns
Select @ID as ID


FETCH NEXT FROM MY_CURSOR INTO @ID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

 

[gmmastros]

1. Before the cursor, create a temp table or table variable.
2. Inside the loop, instead of selecting the data, insert it in to the temp table.
3. After the loop, select the data from the temp table.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

Cursors are evil!

Now explain to me why you need a cursor for this? the code you posted doesn't need one because you can just do this

Select
D.ID
From
[dbo].[Dis] D


what are you doing inside the cursor?



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ecannizzo]

I'm doing a lot of complicated stuff with a lot of data (the example was just for simplicity sake).

 

[SQLDenis]

>>I'm doing a lot of complicated stuff with a lot of data (the example was just for simplicity sake).

Like what? let's see it...or do what George said

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Cursors are evil. There are very few cases where they are necessary. Learn to think set-based!

 

[ecannizzo]

Enlighten me please. Why are cursors evil?

 

[gmmastros]

Cursors are evil because they are slow.

99 times out of 100, cursors can be re-written using set based logic. In almost every case where this occurs, performance increases a lot.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

because a database is meant to be used in terms of sets not in terms of rows

99% of the time there is no need for cursors at all.
a cursor processes the reultds 1 row at atime while a set based solution does all the rows in 1 shot (which is many times faster)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ecannizzo]

I'm going to use the table variable for now, but I have someone looking at my query to decide whether I can do it without the cursor.

Thanks for all your help!

 

[kaht]

You know who likes cursors?


That's right, you know who:

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[gmmastros]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

It's Him

We're not worthy!
We're not worthy!

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[kaht]

That's an awesome emoticon!

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[WoodyGuthrie]

OK...I've seen the Uncle Rico Tongue-Lashing twice in one day. Actually three times if I count the link to the original Uncle Rico google post (where I suspect Uncle Rico still lurks).

We get it. OK? Cursors are "Evil".

But I seem to remember when I registered here...something about this forum that was supposed to be different. The impression I had was that even those of us who are green or who had been led down the evil cursor path would be welcomed and not ridiculed.

Being laughed at...even when it's by an electronic jumpy, bouncing, purple thingy SUCKS!

I greatly appreciate the help I've received here.

Personally, I've never used cursors...Thank God! I might have been laughed right out off the forum.

Have a great weekend!