Dealing with the Exec() command and INSERT

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)

Dealing with the Exec() command and INSERT

Author

Topic

keyurshahit
Starting Member

3 Posts

Posted - 2005-07-15 : 15:51:56

Hi,

Need a quick fix here, a prompt response will be greatly appreciated.
This problem might be very common for some experienced folks out here so shud not take long.

here is the T-SQL batch.

----------------------------------------------

create table ##Active_Inactive_Rows (TableName sysname, Num_Active_RowCnt int, Num_Inactive_RowCnt int)

DECLARE @table_name sysname
DECLARE @temp sysname
DECLARE @sqltext1 varchar(255)
DECLARE @sqltext2 varchar(255)
DECLARE @Active_RowCnt int
DECLARE @Inactive_RowCnt int

DECLARE c1 CURSOR FOR

select so.name
from sysobjects so, syscolumns sc
where so.id=sc.id
and sc.name='tirecordstatus'
and so.name != 'dtproperties'
and so.type='U'

OPEN c1

FETCH NEXT FROM c1
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp = @table_name

-- SELECT @sqltext1 = 'select count(*) from ' + @temp + ' where tirecordstatus=1'

-- SELECT @sqltext2 = 'select count(*) from ' + @temp + ' where tirecordstatus=0'

-- set @Active_RowCnt = 'select count(*) from ' + @temp + ' where tirecordstatus=1'

-- set @Inactive_RowCnt = 'select count(*) from ' + @temp + ' where tirecordstatus=0'

SELECT @Active_RowCnt = count(*) from @temp where tirecordstatus=1

SELECT @Inactive_RowCnt = count(*) from @temp where tirecordstatus=0

INSERT ##Active_Inactive_Rows values ('' + @temp + '', @Active_RowCnt, @Inactive_RowCnt)

-- Select @sqltext3 = 'INSERT ##Active_Inactive_Rows values (''' + @temp + ''', @Active_RowCnt, @Inactive_RowCnt)'

-- EXEC(@sqltext3)

FETCH NEXT FROM c1

INTO @table_name
END

Select * from ##Active_Inactive_Rows

CLOSE c1
DEALLOCATE c1

----------------------------------------------

Basically i m trying to query the system tables to get the names of those tables having a particular column (here in my case 'tirecordstatus'), and than declaring a cursor which will fetch each of these table_names one by one and perform a query on the column 'tirecordstatus' based on its value of 0 or 1
once that is done i m simply tryin to put those results in a temp table.

Everything is fine except the select statement in the while loop does not accepts the table name in the form of a variable (i.e. here @temp)...i hv tried different work arounds to over come this problem (plz see the commented code) but still have not been able to successfully populate my temp table with the values

Plz give me some suggestions, i hv literally tried every possible options within this domain...

Thanks in anticipation

K'ur

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-15 : 16:14:24

This should help you get on the right path. You should be using the INFORMATION_SCHEMA views instead of the system tables. Also, most of us here prefer to use a WHILE loop instead of a cursor as cursors are the devil's spawn



SET NOCOUNT ON

DECLARE @i int, @table_name sysname, @SQL nvarchar(1000), @max int
CREATE TABLE #Temp2 (TableName sysname NULL, TableCount int)

SELECT IDENTITY(int, 1, 1) AS Ident, TABLE_NAME
INTO #Temp1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'name'

SELECT @max = MAX(Ident)
FROM #Temp1

SET @i = 1

WHILE @i <= @max
BEGIN
	SELECT @table_name = TABLE_NAME
	FROM #Temp1
	WHERE Ident = @i
	
	SET @i = @i + 1

	SET @SQL = 'SELECT COUNT(*) FROM ' + @table_name

	INSERT #Temp2(TableCount)
	EXEC(@SQL)

	UPDATE #Temp2 SET TableName = @table_name WHERE TableName IS NULL
END

SELECT * FROM #Temp2

DROP TABLE #Temp1, #Temp2

You can run this code as is. It is just finding all tables that have a column named name. I used that as I knew there were a few system tables that have this column. It's just an example of course. If I find a table that has this column, I do a COUNT(*) and load that along with the table name into a temp table.

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 16:28:40

As an alternative, especially if this is just a one time thing rather than an automated process, you can paste the output of this statement into a QA window and (when you run it) it will perform all your inserts to your pre-existing ##table


select	distinct
'insert	##Active_Inactive_Rows select ''' + table_name + 
''', ActiveRowCnt=sum(active), Inactive=count(*)-sum(active) from' + 
' (select active = case tirecordstatus when 1 then 1 else 0 end from ' + 
table_Name + ') a'
from	information_schema.Columns
where	column_name = 'tirecordstatus'

Be One with the Optimizer
TG

keyurshahit
Starting Member

3 Posts

Posted - 2005-07-15 : 17:25:23

Thank you so much...seems to be working...but actually i need table count on two diff conditions.
therefore the table schema i want is
{
Table_name sysname
rowcount1 int
rowcount2 int
}

so as long as we just hv one rowcount (i.e. tablecount as in ure e.g.) its fine but than what i want is something like this -

SELECT @table_name = TABLE_NAME
FROM #Temp1
WHERE Ident = @i

SET @i = @i + 1

SET @SQL1 = 'SELECT COUNT(*) FROM ' + @table_name + ' where tirecordstatus=1'
SET @SQL2 = 'SELECT COUNT(*) FROM ' + @table_name + ' where tirecordstatus=0'

INSERT #Temp2 (Active_Rows)
EXEC(@SQL1)
INSERT #Temp2(Inactive_Rows)
EXEC(@SQL2)

UPDATE #Temp2 SET TableName = @table_name WHERE TableName IS

NULL
END

-- so the query takes longer time since i ll hv to do updates twice (not shown in the query here)...so is there any way i cud insert both the columns (Active_Rows and Inactive_Rows) in one insert stmt...

Thanks.

quote:
Originally posted by tduggan

This should help you get on the right path. You should be using the INFORMATION_SCHEMA views instead of the system tables. Also, most of us here prefer to use a WHILE loop instead of a cursor as cursors are the devil's spawn .

SET NOCOUNT ON

DECLARE @i int, @table_name sysname, @SQL nvarchar(1000), @max int
CREATE TABLE #Temp2 (TableName sysname NULL, TableCount int)

SELECT IDENTITY(int, 1, 1) AS Ident, TABLE_NAME
INTO #Temp1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'name'

SELECT @max = MAX(Ident)
FROM #Temp1

SET @i = 1

WHILE @i <= @max
BEGIN
SELECT @table_name = TABLE_NAME
FROM #Temp1
WHERE Ident = @i

SET @i = @i + 1

SET @SQL = 'SELECT COUNT(*) FROM ' + @table_name

INSERT #Temp2(TableCount)
EXEC(@SQL)

UPDATE #Temp2 SET TableName = @table_name WHERE TableName IS NULL
END

SELECT * FROM #Temp2

DROP TABLE #Temp1, #Temp2

You can run this code as is. It is just finding all tables that have a column named name. I used that as I knew there were a few system tables that have this column. It's just an example of course. If I find a table that has this column, I do a COUNT(*) and load that along with the table name into a temp table.

Tara

K'ur

keyurshahit
Starting Member

3 Posts

Posted - 2005-07-16 : 11:23:29

Certainly works....sorry didnt looked at ures before replying earlier....Thanks again....

quote:
Originally posted by TG

As an alternative, especially if this is just a one time thing rather than an automated process, you can paste the output of this statement into a QA window and (when you run it) it will perform all your inserts to your pre-existing ##table

select distinct
'insert ##Active_Inactive_Rows select ''' + table_name +
''', ActiveRowCnt=sum(active), Inactive=count(*)-sum(active) from' +
' (select active = case tirecordstatus when 1 then 1 else 0 end from ' +
table_Name + ') a'
from information_schema.Columns
where column_name = 'tirecordstatus'

Be One with the Optimizer
TG

K'ur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources