Master of None

[UPDATE] — SQLServerCentral.com has a great article( from 2002) on the use of Temp Tables. You may find the information useful.

I have gotten a ton of hits some links that I created years ago on T-SQL Cursors. Sometimes they are the the best course of action , but may times they are not the most efficient. This posting has some a possible alternative.

Creating Tables with Identity Columns
I like to create tables and temp tables with identity columns. You can then do row by row process the data using the id column. In another post, I will show you how to create and some uses for Table-Valued functions to provide similar utility found with using cursors. Oh, don’t forget our friend the Case Statement, either.

Below is a an Example of using a temp table with an identity column:
I created a example database then created some very basic and useless views.
The temp table is populated with the values from the sysobjects table for the views.

I then step through the temp table using the identity column id and do a select on the type
and count of each of the views:
�

USE ExampleDB
GO
/*
Created a small datbase and created views of the
sysobjects table based upon the n

*/
IF OBJECT_ID (‘objects_start_with_sys’,’v’) IS NOT NULL
BEGIN
DROP VIEW dbo.objects_start_with_sys
END
GO

CREATE VIEW dbo.[objects_start_with_sys]
AS
Select name ,type
from sysobjects where name like ‘sys%’
GO
IF OBJECT_ID (‘objects_start_with_ob’,’v’) IS NOT NULL
BEGIN
DROP VIEW objects_start_with_ob
END
GO

CREATE VIEW dbo.[objects_start_with_ob]
AS
Select name ,type
from sysobjects where name like ‘ob%’
GO

IF OBJECT_ID (‘objects_start_with_que’,’v’) IS NOT NULL
BEGIN
DROP VIEW objects_start_with_que
END
GO
CREATE VIEW dbo.[objects_start_with_que]
AS
Select name ,type
from sysobjects where name like ‘que%’
GO

IF OBJECT_ID (‘objects_start_with_e’,’v’) IS NOT NULL
BEGIN
DROP VIEW objects_start_with_e
END
GO
CREATE VIEW dbo.[objects_start_with_e]
AS
Select name ,type
from sysobjects where name like ‘e%’
GO

— Drop/Create Temp Table with Identity Column for Order
IF OBJECT_ID (‘TEMPDB..#GetViews’,’u’) IS NOT NULL
BEGIN
DROP TABLE #GetViews
END
BEGIN
CREATE TABLE #GetViews(
id INT IDENTITY(1,1),
viewName VARCHAR(40) NOT NULL
)
END

INSERT INTO #GetViews
Select RTRIM(name)viewName from sysobjects
where type=’v’
order by NAME DESC

SELECT * from #GetViews

DECLARE @I INT , @MaxI INT

SELECT @I = 1
,@MaxI = MAX(id) from #GetViews

WHILE @I <= @MaxI
BEGIN
DECLARE @sqlStatement NVARCHAR(150)
— Build Select Statement
SELECT @sqlStatement = ‘Select type, Count(type) as [Count_items from_’+UPPER(RTRIM(viewName)) +’] from ‘ + RTRIM(viewName)+ ‘ Group By Type Order by Count(type) DESC’
from #GetViews where id = @I
— Run Select Statement
EXECUTE sp_ExecuteSQL @sqlStatement
— Increment @I to Next Value
Select @I = @I +1
END

Select id
,viewname
from #GetViews order by viewname

— Drop Temp Table with Identity Column for Order
IF OBJECT_ID (‘TEMPDB..#GetViews’,’u’) IS NOT NULL
BEGIN
DROP TABLE #GetViews
END

Tiny URL for this post:
 Copy