如何将临时表数据读入游标 [英] How to read temporary table data into cursor

问题描述

我有一个sql server表说table_student为

Hi, I have a sql server table say "table_student" as

CREATE TABLE table_student
   (	StudentFirstName Varchar(200),
       StudentLastName  Varchar(200),
       StudentEmail     Varchar(50)
   )

我在每个对应列的整个表中只有一行，

and I have only one row in entire table for each corresponding column as,

Insert into table_student (StudentFirstName)
   Values('Ram')

现在很明显，StudentLastName和StudentEmail列的值为null e在相应的行中。

我正在写Store Store以获取具有null值的列列表如下

Now its obvious that column StudentLastName and StudentEmail have null value in their corresponding rows.
I am writing following Store Procedure to get the list of column having null value as follow

create Procedure InsertStudentrecord

As
 Begin 
 
 
   declare @col varchar(255), @cmd varchar(max)

	DECLARE getinfo cursor for
	SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
	WHERE t.Name = 'table_student'

OPEN getinfo

FETCH NEXT FROM getinfo into @col
 print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM table_student WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo
  
 End

我得到列名StudentLastName，StudentEmail和fetch status = 0，这当然是正确的结果。但当我用临时表替换同一个表时说

temp_student与完全相同的行

即

and I get column name StudentLastName, StudentEmail and fetch status= 0 which is of course correct result. But when I replace the same table with temporary table say
"temp_student" with exactly same row
i.e

Insert into temp_student(StudentFirstName) Values('Ram')

我做当我查找-1时，没有获得任何具有空行的列并且获取状态= -1我发现它意味着FETCH语句失败或行超出了结果集。



我需要使用临时表请帮助我。

I do not get any column with null row and fetch status= -1 when I looked for -1 I found that it mean "The FETCH statement failed or the row was beyond the result set."

I need to use temp table Please help me in that.

推荐答案

试试这个吗？

try this?

create Procedure InsertStudentrecord
 
As
 Begin 
 
 
   declare @col varchar(255), @cmd varchar(max)
 
	DECLARE getinfo cursor FAST_FORWARD for
	SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
	WHERE t.Name = 'table_student'
 
OPEN getinfo
 
FETCH NEXT FROM getinfo into @col

 print @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM table_student WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)
 
    FETCH NEXT FROM getinfo into @col
END
 
CLOSE getinfo
DEALLOCATE getinfo
  
 End

我认为..

你想要具有nulll值的列，并且你想将值设置为相应的字段。

我是对的???



所以，只需运行一个简单的Sql查询来获取您想要设置的相应列，并使用它所需的值并更新专栏。

就是这样。

喜欢这样..

i think..
you want the columns with nulll value and you want to set the value to the corresponding field.
am i right???

so, just run a simple Sql Query to fetch the corresponding column which one you want to set with it's desired value and update the column.
thats it.
like this..

DECLARE c1 cursor for select Id,colum1,colum2 from table1 where column1 is null or colum2 is null
open c1

fetch next from c1 into @var1,@var2
WHILE @@FETCH_STATUS = 0
Begin
if @var1 is null
  begin
  // Update Query for column1
  end
if @var2 is null
  begin
  // Update Query for column2
  end

fetch next from c1 into @var1,@var2
End
CLOSE getinfo
DEALLOCATE 

这篇关于如何将临时表数据读入游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！