Copy query result into excel sheet.

Copy query result from SQL Server Managment studio.
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!2792.entry

Using multiple CTE 's in single SQL Statement

Declare @table table (id int,name varchar(50),parentid int) Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
Insert into @table values (9,'Sales Manager',7)
Insert into @table values (9,'Sales Manager',4);

with CTE(id ,name,parentid,levels) as
(Select id,name,parentid ,1 as levels from @table t where parentid =1
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
),

CTE1(id ,name,parentid,levels) as
(
Select id,name,parentid ,1 as levels from @table t where parentid =7
union all
Select td.id,td.name,td.parentid,levels+ 1 as levels from @table td inner join cte on td.parentid = cte.id
)

select CTE.* from CTE inner join CTE1 on CTE.id = CTE1.id
 
cheers:)

Using CTE in Sql Server 2005 / 2008

Declare @table table (id int,name varchar(50),parentid int)
Insert into @table values (1,'Director',NULL)
Insert into @table values (4,'City Manager Fsd',3)
Insert into @table values (5,'Area Manager Fsd',4)
Insert into @table values (2,'County Manager',1)
Insert into @table values (3,'Country Manager 2',1)
Insert into @table values (6,'City Manager ISB',2)
Insert into @table values (7,'Co-director',NULL)
Insert into @table values (8,'Country Head',7)
;

with CTE(id ,name,parentid,level) as

(



Select id,name,parentid ,1 as level from @table t where parentid is null

union all

Select td.id,td.name,td.parentid,level+ 1 as level from @table td inner join cte on td.parentid = cte.id

)


select * from CTE


Find answers of your questions in this advance technique of SQL Server

SQL Server ETL tool proves to be best in market.

Insertion of 1TB of data with UniSys in less than 30 minutes beating informatica which made this in 45minutes.

Here is more detail.
http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

Update Statement Using Join in from clause

Some time we need to update our table from another table join that cannot possible with simple select table. In sql server 2005 we can do this using update using join as I show this in my example.
Declare @vA table (id int,val varchar(50))
Declare @vb table (id int,val varchar(50))

Insert into @vA(id,val) values (1,'1111')
Insert into @vA(id,val) values(2,'1111')
Insert into @vA(id,val) values(3,'1111')
Insert into @vA(id,val) values(4,'1111')
Insert into @vA(id,val) values(5,'1111')
Insert into @vA(id,val) values(6,'1111')
Insert into @vB(id,val) values(1,'2222')
Insert into @vB(id,val) values(2,'4444')
Insert into @vB(id,val) values(3,'5555')
Insert into @vB(id,val) values(4,'3333')
Insert into @vB(id,val)values (5,'6666')
Insert into @vB(id,val) values(6,'7777')

Update @vA set val=d.val
from @vA a inner join @vB d on a.id = d.id

Select * from @vA


enjoy the output.

Store procedure optimization -- best practices

1. Use stored procedures instead of heavy-duty queries.

2. Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
3. Call stored procedure using its fully qualified name.
4. Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
6. Use the sp_executesql stored procedure instead of the EXECUTE statement.
7. Use sp_executesql stored procedure instead of temporary stored procedures.
8. If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
9. Try to avoid using temporary tables inside your stored procedure.
10. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
12. Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
 
 
For more detail please visit.http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm