dynamic loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

dynamic loop

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2004-04-02 : 12:46:28

I'm trying to count the number of records for several differnt tables. I have a table with the names of these table that I want to loop through. Can't seem to get this to work:

declare @tableID int
declare @tableName varchar(100)
declare @sql varchar(100)
declare @j varchar(100)
set @tableID = 0

while @tableID < 3
begin
set @tableID = @tableID + 1

set @tableName = 'select tablename from count_these_tables where tableid='
set @tableName = @tableName + convert(varchar(2),@tableID)
exec(@tableName)
print @tableName

set @sql = 'select count(item) as '
print(@sql)
set @sql = @sql + @tableName + 'from [' + @tableName + '] with (nolock)'
print(@sql)
exec @sql
end
=================
I get this error:
select tablename from count_these_tables where tableid=1
select count(item) as
select count(item) as select tablename from count_these_tables where tableid=1from [select tablename
Server: Msg 203, Level 16, State 2, Line 20
The name 'select count(item) as select tablename from count_these_tables where tableid=1from [select tablename' is not a valid identifier.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-02 : 12:51:35

Are you going to use this in an application or just in Query Analyzer:

SELECT 'SELECT COUNT(*) FROM ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Tara

lane0618
Posting Yak Master

134 Posts

Posted - 2004-04-02 : 13:08:47

Just need the query for a stored proc.
Ultimately, what I need is to put the results in a seperate table. How do I go about executing the statements you provided, inserting the results in a record in a result table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-02 : 13:13:03

You would just use INSERT INTO..SELECT COUNT(*)...

I'll work on the code now to loop through your table.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-02 : 13:18:35

quote:
Originally posted by tduggan

You would just use INSERT INTO..SELECT COUNT(*)...

I'll work on the code now to loop through your table.

Tara

Yea, like:



USE Northwind
GO

SET NOCOUNT ON

DECLARE @sql varchar(8000)

CREATE TABLE #myTable99(Col1 int, Col2 sysname)

DECLARE myCursor99 CURSOR
FOR
SELECT 'INSERT INTO #myTable99(Col1, Col2) SELECT COUNT(*) , ' 
	+ '''' + TABLE_NAME + '''' 
	+ ' FROM [' + TABLE_NAME + ']' AS sql
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @sql

WHILE @@FETCH_STATUS = 0
  BEGIN
--	SELECT @sql	
	EXEC(@sql)
	FETCH NEXT FROM myCursor99 INTO @sql
  END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT * FROM #myTable99

DROP TABLE #myTable99

SET NOCOUNT OFF

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-02 : 13:23:46

Here's one without a cursor:



SET NOCOUNT ON

CREATE TABLE count_these_tables
(
TableID INT,
Item SYSNAME
)

CREATE TABLE Counts
(
Item SYSNAME,
NumOfRows INT
)

INSERT INTO count_these_tables
SELECT 1, 'ASSETS'
UNION ALL
SELECT 2, 'GT_PARM_VAL'
UNION ALL
SELECT 3, 'MAINT_HISTORY'

DECLARE @id INT
DECLARE @maxid INT
DECLARE @SQL VARCHAR(7000)

SELECT @id = MIN(TableID), @maxid = MAX(TableID)
FROM count_these_tables

WHILE @id <= @maxid
BEGIN
	
	SELECT @SQL = 'INSERT INTO Counts SELECT ''' + item + ''', COUNT(*) FROM ' + item
	FROM count_these_tables
	WHERE TableId = @id

	EXEC (@SQL)

	DELETE FROM count_these_tables
	WHERE TableId = @id

	SELECT @id = MIN(TableID)
	FROM count_these_tables

END

SELECT Item, NumOfRows
FROM Counts

DROP TABLE count_these_tables
DROP TABLE Counts

I used three tables to count that I already had in a user database.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-02 : 16:12:38

Here is one without a cursor or a loop:


INSERT INTO Counts 
SELECT SO.Name as Tables, SI.rows as Rows
FROM sysindexes as SI
INNER  JOIN sysobjects SO on SO.id = SI.id AND SO.type = 'U'
WHERE (SI.indid = 0 OR SI.indid = 1) and SO.Name != 'dtproperties' 
AND SO.Name IN (SELECT Item FROM count_these_tables)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-04-02 : 16:28:58

LOL the last "exec @sql" must be "exec (@sql)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources