InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 15:48:16 GMT20http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxHi Joe,Your problem is #tmptbl2 is only present in the scope of the SQL executed via EXEC (@SQL1-4), and is out of scope when you try to select from it.You can create the temp table outside this & it will be available to the dynamic & fixed SQL.Something like this:[code="sql"]IF object_id('tempdb..#tmptbl1') Is not nulldrop table #tmptbl1IF object_id('tempdb..#tmptbl2') Is not nulldrop table #tmptbl2create table #tmptbl2 (table_name varchar(100), AO_Counts INT, AE_Counts INT...-- load yes information into temp tblselect 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 NGinto #tmptbl1from stage.dbo.itapdb_ctrl_twhere AO = 'Y' or AE = 'Y' or AR = 'Y' or NG = 'Y'[/code]Rewrite your Dynamic bits to:[code="sql"]Select @SQL = 'insert into #tmptbl2 (table_name, AO_Counts)select '''+@tablename+''' as table_name, count (*) as AO_CountsFROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t bwhere a.total_army_comp_cd = ''A''and b.mil_pers_clas_cd = ''O'')'Select @SQL2 = 'insert into #tmptbl2 (table_name, AE_Counts)select '''+@tablename+''' as table_name, count (*) as AE_CountsFROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t bwhere a.total_army_comp_cd = ''A''and b.mil_pers_clas_cd = ''E'')'[/code]etcThen at the end, select * from #tmptbl2 should return results.Hope that makes sense & note I've split AO, AE counts into seperate fields. You'll need to rework this to the format you require.ThanksGazThu, 06 Dec 2012 06:50:27 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxI tried creating a table to do the same thing, but got the same error.I need to populate the table, and then view the results. Any help is appreciated.Thanks,JoeThu, 06 Dec 2012 05:47:44 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxJumping 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 [b]not[/b] 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.Fri, 23 Nov 2012 04:34:23 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxHere it is. Thanks for helping:IF object_id('tempdb..#tmptbl1') Is not null drop table #tmptbl1IF object_id('tempdb..#tmptbl2') Is not null drop table #tmptbl2 -- load yes information into temp tblselect 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 GOMon, 19 Nov 2012 11:33:48 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspx[quote][b]jbm6401 (11/19/2012)[/b][hr]So if this is the case, is there any way around the dynamic SQL? Thanks.[/quote]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.Mon, 19 Nov 2012 08:31:11 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxSo if this is the case, is there any way around the dynamic SQL? Thanks.Mon, 19 Nov 2012 07:15:25 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxIf 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.Thu, 15 Nov 2012 08:50:40 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspx[quote][b]jbm6401 (11/15/2012)[/b][hr]Actually, i cannot provide the code. Let me see what I can do with a do while loop.Thanks,[/quote]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.Thu, 15 Nov 2012 08:49:22 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxActually, i cannot provide the code. Let me see what I can do with a do while loop.Thanks,Thu, 15 Nov 2012 08:40:19 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspx[quote][b]jbm6401 (11/15/2012)[/b][hr]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[/quote]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.Thu, 15 Nov 2012 08:06:31 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxIn 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, JoeThu, 15 Nov 2012 08:02:17 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspx[quote][b]jbm6401 (11/15/2012)[/b][hr]Figured it out. Thanks for your help.[/quote]What didd you figure out and can you post your solution?Thu, 15 Nov 2012 07:42:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxFigured it out. Thanks for your help.Thu, 15 Nov 2012 07:02:57 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxI 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.Thu, 15 Nov 2012 06:50:58 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxThis 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.Thu, 15 Nov 2012 06:45:29 GMTjbm6401http://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxgo 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:[code]SELECT so.[name] as [table name] , 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][/code]Thu, 15 Nov 2012 06:33:03 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1385119-1292-1.aspxBefore 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.JoeThu, 15 Nov 2012 06:29:08 GMTjbm6401