仅获取列中的第一个值

27 浏览
0 Comments

仅获取列中的第一个值

CREATE PROC [dbo].[usp_InsertGenerator]

AS

BEGIN

DECLARE @tablename varchar(max)

DECLARE cursCol1 CURSOR FOR

SELECT name FROM sys.tables

OPEN cursCol1

FETCH NEXT FROM cursCol1 INTO @tablename

DECLARE cursCol CURSOR FOR

SELECT column_name, data_type

FROM information_schema.columns

WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000)

DECLARE @stringData nvarchar(3000)

DECLARE @dataType nvarchar(1000)

SET @string='INSERT '+@tableName+'('

SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0

BEGIN

PRINT 'Table '+@tableName+' not found, processing skipped.'

--FETCH NEXT FROM cursCol1 INTO @tablename

CLOSE curscol

DEALLOCATE curscol

RETURN

END

WHILE @@FETCH_STATUS=0

--FETCH NEXT FROM cursCol1 INTO @tablename

BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

SET @stringData=@stringData+'''''''''+

isnull('+@colName+','''')+'''''',''+'

END

ELSE

BEGIN

SET @stringData=@stringData+'''''''''+

isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'

END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName ,@dataType

END

BEGIN

DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')

VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''

FROM '+@tableName

PRINT (@query)

FETCH NEXT FROM cursCol1 INTO @tablename

CLOSE cursCol1

DEALLOCATE cursCol1

CLOSE cursCol

DEALLOCATE cursCol

END

END

GO

在执行过程时，我只能从sys.tables的name列中得到第一个值（行）。

我想要得到sys.tables中所有行的值。

查询应该逐个获取每个表名，然后为每个表执行查询，以便我可以得到所有表的插入语句。

提前致谢。

0

1 答案

0 Comments

在上述代码中，出现了(Only fetching first value in column)的问题。这个问题的原因是在使用游标获取name时，没有使用FETCH NEXT来获取下一个值。解决方法是在FETCH NEXT后添加INTO语句来指定获取的值的存储位置。

修复后的代码如下所示：

CREATE PROC [dbo].[usp_InsertGenerator]
AS
BEGIN
  DECLARE @tableName varchar(max)
  DECLARE @insertTable table (insertVal varchar(max))
  DECLARE cursCol1 CURSOR FOR
  SELECT name FROM sys.tables 
  OPEN cursCol1
  FETCH NEXT FROM cursCol1 INTO @tableName
  WHILE @@FETCH_STATUS=0
  BEGIN
      DECLARE cursCol CURSOR FOR
      SELECT column_name, data_type
      FROM information_schema.columns 
      WHERE table_name = @tableName
      OPEN cursCol
      DECLARE @columnName nvarchar(3000) 
      DECLARE @dataType nvarchar(3000)
      DECLARE @insertStatement nvarchar(1000)
      SET @insertStatement ='INSERT '+ @tableName +'('
      SET @columnName =''
      DECLARE @value nvarchar(50)
      FETCH NEXT FROM cursCol INTO @columnName, @dataType
      IF @@FETCH_STATUS<>0
      BEGIN
          PRINT 'Table '+ @tableName +' not found, processing skipped.'
          FETCH NEXT FROM cursCol1 INTO @tableName
          CLOSE curscol
          DEALLOCATE curscol
          RETURN
      END
      WHILE @@FETCH_STATUS=0
      BEGIN
          IF @dataType in ('varchar','char','nchar','nvarchar')
          BEGIN
              SET @value =+'''''''''+
                      isnull('+@columnName+','''')+'''''',''+'
          END
          ELSE
          BEGIN
            SET @value =+'''''''''+
          isnull(cast('+@columnName+' as varchar(200)),''0'')+'''''',''+'
          END
          SET @insertStatement = @insertStatement + @columnName +','
          FETCH NEXT FROM cursCol INTO @columnName, @dataType
      END
        BEGIN
              DECLARE @execStatement nvarchar(4000)
              SET @execStatement ='SELECT '''+substring(@insertStatement,0,len(@insertStatement)) + ') 
                  VALUES(''+ ' + substring(@value,0,len(@value)-2)+'''+'')'' 
                  FROM '+ @tableName
              INSERT INTO @insertTable (insertVal) EXEC (@execStatement)
              -- PRINT (@execStatement)
          END
      CLOSE cursCol
      DEALLOCATE cursCol
    FETCH NEXT FROM cursCol1 INTO @tableName
    END
    CLOSE cursCol1
    DEALLOCATE cursCol1  
    SELECT * FROM @insertTable
END

这样修复后的代码将会正确获取所有表的insert语句，并且将结果存储在一个临时表中。

0