Could not find any table

Ramco Ramco

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain3y ago

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 Ramco3y ago
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 Swain3y ago

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 Ramco3y ago
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 Swain3y ago
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.