November 15, 2012 at 6:29 am
Before you chastise me on not doing cursors, since I scanned articles before submitting this request, I know cursors are not that fast. But, this is overall a small table and should not be a hassle.
I am doing a cursor to get table names from a table we use to generate data. The @SQL command brings in a variable representing the table name and uses this to query a count of records in that table. The end game is the check on the table to see if it has been populated. That comes later.
What I would like to do is to have in the results pane the name of the variable representing the table name, and the corresponding counts of elements in that table. The intent is to send this to a temporary table to where I can analyze that to make sure the tables have the counts mentioned in the second paragraph.
Right now, I need to just figure out how to get the table name and the counts into the temp table via the cursor.
Thanks for your help.
Joe
November 15, 2012 at 6:33 am
go ahead and throw your cursor away.
the data you are looking for is already materialized for you;
the indexes maintain a count of the number of rows for every table.
try this and see if it's not doing exactly what you were trying to do with your cursor:
SELECT so.[name] as
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
Lowell
November 15, 2012 at 6:45 am
This may help. What we do is a daily ETL and the tables created. Then we create a table where we send the table name and the record count on what we do. I'll see what I can do with this. Thanks.
November 15, 2012 at 6:50 am
I guess more information on what I'm trying to get would help.
The table we have actually has the counts, but we have multiple sources. The cursor gets the table names and goes against the actual tables created and counts the records based on the sources. This is a way we try to validate the sources loaded the data into our table.
Does that help?
Thanks.
November 15, 2012 at 7:02 am
Figured it out. Thanks for your help.
November 15, 2012 at 7:42 am
jbm6401 (11/15/2012)
Figured it out. Thanks for your help.
What didd you figure out and can you post your solution?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2012 at 8:02 am
In my select @SQL statement, I needed to put extra single quotes around the variable name. In this case: '''+@tablename+'''
Now my problem is I cannot select from the temporary table I created. I tried creating a permanent table, and the same thing. I cannot view the data because the error messsage says "invalid object name #tmptbl2".
So it appears cursors don't like creating tables.
Now I need to know if a Do While loop will populate a temp or perm table which can be viewed later.
I'm dealing in SQL Server 2008 Release 2 DB.
Thanks, Joe
November 15, 2012 at 8:06 am
jbm6401 (11/15/2012)
In my select @SQL statement, I needed to put extra single quotes around the variable name. In this case: '''+@tablename+'''Now my problem is I cannot select from the temporary table I created. I tried creating a permanent table, and the same thing. I cannot view the data because the error messsage says "invalid object name #tmptbl2".
So it appears cursors don't like creating tables.
Now I need to know if a Do While loop will populate a temp or perm table which can be viewed later.
I'm dealing in SQL Server 2008 Release 2 DB.
Thanks, Joe
If you could post the actual code you are running, ddl for the table(s) involved, sample data and desired output I am 100% certain you do not need anything as complicated, or slow, as a cursor for this.
Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 15, 2012 at 8:40 am
Actually, i cannot provide the code. Let me see what I can do with a do while loop.
Thanks,
November 15, 2012 at 8:49 am
jbm6401 (11/15/2012)
Actually, i cannot provide the code. Let me see what I can do with a do while loop.Thanks,
A while loop is no better than a cursor for performance. Not sure why you can't post the code. I can understand not posting real data, just modify the data to simulate the problem. Certainly don't post data that might be confidential.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 15, 2012 at 8:50 am
If I read it right, you're executing dynamic SQL (hence the @SQL variable)?
Your temp table will be out of scope from within that execution - hence the error you're getting.
You may experience the same issue using a while loop if you're still using dynamic SQL.
November 19, 2012 at 7:15 am
So if this is the case, is there any way around the dynamic SQL? Thanks.
November 19, 2012 at 8:31 am
jbm6401 (11/19/2012)
So if this is the case, is there any way around the dynamic SQL? Thanks.
The way around it is to get rid of the cursor. We can't help you because you "can't" post the code. You have 3 very knowledgeable people plus myself willing to help but we have no details to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2012 at 11:33 am
Here it is. Thanks for helping:
IF object_id('tempdb..#tmptbl1') Is not null
drop table #tmptbl1
IF object_id('tempdb..#tmptbl2') Is not null
drop table #tmptbl2
-- load yes information into temp tbl
select ltrim(rtrim(table_name))as table_name,
case when AO = 'Y' Then 'AO' end as AO,
case when AE = 'Y' Then 'AE' end as AE,
case when AR = 'Y' Then 'AR' end as AR,
case when NG = 'Y' Then 'NG' end as NG
into #tmptbl1
from stage.dbo.itapdb_ctrl_t
where AO = 'Y' or AE = 'Y' or AR = 'Y' or NG = 'Y'
--select * from #tmptbl1 t
Declare @tablename varchar(500),
@AO varchar(2),
@AE varchar(2),
@AR varchar(2),
@NG varchar(2),
@SQL varchar (500),
@SQL2 varchar (500),
@SQL3 varchar (500),
@SQL4 varchar (500),
@tbl_counts varchar (500),
@total_tbl_Count varchar(500);
DECLARE table_cursor CURSOR FOR
SELECT
t.table_name, t.ao, t.ae, t.ar, t.ng
FROM
#tmptbl1 AS t
--WHERE
--t.ao = 'Y' or t.ae = 'Y' or t.ar = 'Y' or t.ng = 'Y'
ORDER BY
t.table_name
Open table_cursor;
FETCH NEXT FROM table_cursor
INTO @tablename, @AO, @AE, @AR, @NG;
WHILE @@FETCH_STATUS = 0
Begin
Print @tablename
Print @AO
Print @AE
Print @AR
Print @NG
If @AO = 'AO' begin
Select @SQL = '
select '''+@tablename+''' as table_name, count (*) as AO_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''O'')'
Print @SQL
end
If @AE = 'AE' begin
Select @SQL2 = 'select '''+@tablename+''' as table_name, count (*) as AE_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''E'')'
end
Print @SQL2
IF @AR = 'AR' begin
Select @SQL3 = 'select '''+@tablename+''' as table_name, count (*) as AR_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''V''
and b.mil_pers_clas_cd in (''E'',''O'',''W''))'
end
Print @SQL3
If @NG = 'NG' begin
Select @SQL4 = 'select '''+@tablename+''' as table_name, count (*) as NG_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''G''
and b.mil_pers_clas_cd in (''E'',''O'',''W''))'
end
Print @SQL4
exec (@SQL);
exec (@SQL2);
exec (@SQL3);
exec (@SQL4)
-- Clear out SQL statements for next iteration
Select @SQL = ' '
Select @SQL2 = ' '
Select @SQL3 = ' '
Select @SQL4 = ' '
FETCH NEXT FROM table_cursor
INTO @tablename, @AO, @AE, @AR, @NG
END
CLOSE table_cursor
DEALLOCATE table_cursor;
select * from #tmptbl2
GO
November 23, 2012 at 4:34 am
Jumping in late on this party - I agree entirely with not using cursors loops etc.
A "quick and dirty" solution would be to create the tables as permanent table not in tempdb as # tables at the beginning of your code, and drop them at the end.
This way they would be visible to to the dynamic SQL.
But the others are right - not the most efficient way forwards.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply