Yes, a global temp table is an option. Just have to remember the concurrency issue - can't have multiple processes executing the code concurrently, or have different code using the...

sowmya.br (12/16/2008)

Only thing is i donot want to create #a like below

CREATE TABLE #a(

...

Use "left join" instead of "inner join" on the derived tables.

How are you executing your simple TSQL to get the error? In an SSMS query window? I've seen this error when executing from a SQL Agent job because the SQL...

The query will not make use of an index on (eno, DOB, dt_log). Try it.

Perhaps you want to add to every table a new index...

I read that functions cannot return text datatype, at least in sql2000.

It's not particularly simple to do that stuff. But here's a sample you can work with.

-- sample table and data.

declare @tbl table (id int, trans_desc varchar(20), trans_date datetime, trans_id...

The text datatype means this must be done procedurally, i.e. in a loop of some kind.

I'm not sure, but something like this may be more efficient outside of SQL...

What is the max that will be concatenated, i.e. the destination column datatype? char(8000), varchar(max), text?

What version of SQL Server? SQL2005 and SQL2008 have more features related to this task.

What version of SQL Server? SQL2005+ has some new stuff that helps will these kind of queries.

I generally use TOP to do something like that. For example:

SELECT TOP 3

id,

desc,

trans_date

FROM

...

I see what you're doing, David.