Could not find any table

Question

Hi

In Pivot if i do'nt insert into #temp then it workd properly otherwise it gives error 'Could not finf any table[0]

--set @query = 'select [BookId] [Book ID],BookTitle [Book],[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],[ClosingStock], ' + @cols + '
	--INTO #temp from (select [BookID] ,BookTitle,[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],Status,[ClosingStock],StudentName from #finalTable
	--where (('''+@BookID+'''=''0'' OR [BookId] IN('+ cast(@BookID as varchar(Max))+')))) x pivot
	--( Max(Status) for StudentName in (' + @cols + ')) piv order by BookTitle';
	print @query
	execute (@query)

DataTable BookPlanningResult = sQLUtility.GeBookPlanningRecommendation(vRpl, BookCollection, StudentCollection).Tables[0];
                if (BookPlanningResult != null)
                {
                    DataColumn Remarks = new DataColumn("Remarks", typeof(string));
                    BookPlanningResult.Columns.Add(Remarks);
                    DataColumn Dates = new DataColumn("Date", typeof(DateTime));
                    BookPlanningResult.Columns.Add(Dates);

                    grdPlanning.DataSource = BookPlanningResult;
                    grdPlanning.DataBind();
                }

Thanks

Rajkiran Swain · Answer

To insert the pivot data into a permanent or other table, you can modify the code in the `WHILE` loop to insert the values into the desired table. Here's an example: -- Declare a cursor to iterate through the rows of #temp table DECLARE cur CURSOR FOR SELECT [Book ID], [Book], [ComboBookName], [CoreRPL], [Lexile], [NumberOfPages], [SessionPlanStatus], [ClosingStock] FROM #temp -- Declare variables to hold the values of each row DECLARE @bookId INT DECLARE @bookTitle VARCHAR(100) DECLARE @comboBookName VARCHAR(100) DECLARE @coreRPL VARCHAR(100) DECLARE @lexile VARCHAR(100) DECLARE @numberOfPages INT DECLARE @sessionPlanStatus VARCHAR(100) DECLARE @closingStock INT -- Open the cursor and fetch the first row OPEN cur FETCH NEXT FROM cur INTO @bookId, @bookTitle, @comboBookName, @coreRPL, @lexile, @numberOfPages, @sessionPlanStatus, @closingStock -- Loop through the rows of #temp table and insert into the desired table WHILE @@FETCH_STATUS = 0 BEGIN -- Insert the row into the desired table INSERT INTO MyTable (BookID, BookTitle, ComboBookName, CoreRPL, Lexile, NumberOfPages, SessionPlanStatus, ClosingStock) VALUES (@bookId, @bookTitle, @comboBookName, @coreRPL, @lexile, @numberOfPages, @sessionPlanStatus, @closingStock) -- Fetch the next row FETCH NEXT FROM cur INTO @bookId, @bookTitle, @comboBookName, @coreRPL, @lexile, @numberOfPages, @sessionPlanStatus, @closingStock END -- Close and deallocate the cursor CLOSE cur DEALLOCATE cur ``` Replace `MyTable` with the name of the table where you want to insert the pivot data, and specify the correct column names and data types.

Ramco Ramco · Answer

Hi Rajkiran As Student column are dynamic as u see Pivot iutput. how i can insert Pivot data in permanenr or other table Thanks

Rajkiran Swain · Answer

It seems like you have commented out the creation of the #temp table in your query, but you are still trying to reference it in the pivot clause. Uncommenting that line should resolve the error. Regarding using the #temp table in a cursor, you can use the following code as an example: -- Declare a cursor to iterate through the rows of #temp table DECLARE cur CURSOR FOR SELECT [Book ID], [Book], [ComboBookName], [CoreRPL], [Lexile], [NumberOfPages], [SessionPlanStatus], [ClosingStock] FROM #temp -- Declare variables to hold the values of each row DECLARE @bookId INT DECLARE @bookTitle VARCHAR(100) DECLARE @comboBookName VARCHAR(100) DECLARE @coreRPL VARCHAR(100) DECLARE @lexile VARCHAR(100) DECLARE @numberOfPages INT DECLARE @sessionPlanStatus VARCHAR(100) DECLARE @closingStock INT -- Open the cursor and fetch the first row OPEN cur FETCH NEXT FROM cur INTO @bookId, @bookTitle, @comboBookName, @coreRPL, @lexile, @numberOfPages, @sessionPlanStatus, @closingStock -- Loop through the rows of #temp table and do processing WHILE @@FETCH_STATUS = 0 BEGIN -- Do processing here using the values of the current row PRINT @bookTitle -- Fetch the next row FETCH NEXT FROM cur INTO @bookId, @bookTitle, @comboBookName, @coreRPL, @lexile, @numberOfPages, @sessionPlanStatus, @closingStock END -- Close and deallocate the cursor CLOSE cur DEALLOCATE cur

Ramco Ramco · Answer

hi Rajkiran Can u pls gudie what changes sjould i made in code. In one of my threads also i want to further use this #temp table for Cursor processing Thanks

Rajkiran Swain · Answer

Based on the code provided, it seems that you are trying to execute a SQL query that includes a PIVOT operation to transform rows into columns. However, you are getting an error message that says "Could not find any table[0]" when you insert the results into a temporary table. One possible reason for this error is that the temporary table #temp does not exist or is not accessible when you try to insert the results into it. This could be due to a scope issue, such as creating the temporary table in a different session or transaction than the one where you are executing the query. To fix this issue, you may need to ensure that the temporary table #temp is created and accessible in the same session or transaction where you execute the query. You can also try using a different name for the temporary table to avoid conflicts with other tables or procedures. Additionally, please note that executing dynamic SQL queries using EXECUTE can pose security risks if the input parameters are not properly sanitized or validated. It is recommended to use parameterized queries or stored procedures to prevent SQL injection attacks.