Inserting Serial No Column in result of a query

Hello Friends

My problem is

Suppose Query is - Select * from tbl_Employee

TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST

I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.

Serial Name Post

1 XYZ QER

2 SDF OPO

3 WER IPO

If any body knows please post the solution its urgent.

Where is the serial number supposed to come from?
Jason
|||http://www.aspfaq.com/show.asp?id=2427
|||

Serial Number is supposed to be generated through query only.

|||

Using the #temp table option described in the article will achieve that for you.

CREATE TABLE

   #temp

(

   Rank INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

   firstName VARCHAR(50),

   lastName VARCHAR(50)

)

INSERT INTO

   #temp

(

   firstName,

   lastName

)

SELECT

   firstName,

   lastName

FROM

     people

ORDER BY

     lastName,

     firstName

SELECT

     *

FROM

     #temp

ORDER BY

     Rank

DROP TABLE #temp

|||I take it no one bothered reading the link I posted..?
|||Yes I did. I just recommended the #temp table approach in it. Thething I didn't like about the way it was done in the article, however,is that it does not create the #temp table first. For performancereasons any #temp table should be explicitly created, not created by aSELECT INTO.
|||What performance reasons?
That may have been true in previous versions of SQL Server (some lockswere placed on tempdb during temp table creation), but it's not anissue in SQL Server 2000.
|||There are a few reasons to my understanding:
the SELECT INTO method will always require a stored procedure recompilation; the explicit CREATE TABLE will not necessarily
|||Regarding recompilation, I don't think SELECT INTO necessarily causes it. I see no difference in behavior between thefollowing three sprocs, monitoring the SQL Compilations/sec and SQLRe-Compilations/sec counters and tracing the SP:Recompile event (on anotherwise dead server):

create proc xxx

as

    select 1 as somecol

    into #blah

GO

create proc yyy

as

    create table #blah(somecol int)

    insert #blah values (1)

GO

create proc zzz

as

    declare @.blah table(somecol int)

    insert @.blah values (1)

GO

I ran each of these numerous times and saw no recompiles with any ofthem. I'm not aware of whether or not SELECT INTO is more likelyto recompile in certain cases, but merely using the syntax does notcause re-compilation.

|||By the way, just to clarify, there are no "non-logged" operations inSQL Server. There are "minimally-logged" operations, and SELECTINTO can be minimally-logged -- but only if the database is in simplerecovery mode. Log shipping ( == "trucking" ?), AFAIK, does notapply to databases set to simple recovery mode. Correct me if I'mwrong, though -- I'm not a log shipping expert. So again I don'tthink this is a problem with SELECT INTO.

|||Yes, I meant log shipping. And since my use of SELECT INTO oncecaused our DBA to have to work through the night I have stayed far awayfrom it. The recovery model on the database might very well havechanged since then.
Thanks for your insight. Hopefully the original poster now has what they need. :-)