Only fetching first value in column

Question

  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)
          CLOSE cursCol1
          DEALLOCATE cursCol1
          CLOSE cursCol
          DEALLOCATE cursCol
      END
  END
  GO

When executing the procedure I am only getting first value(Row) from name column in sys.tables. I want it for all the rows in the sys.tables. Query should take each table name one by one then execute the query for every table so that I can get insert statement for all the tables. Thanks in advance .

If you want the first row, then use `TOP 1` and add an `ORDER BY` to guarantee consistent results. But you were [already told this](https://stackoverflow.com/questions/67682555/error-in-stored-procedure-for-generate-insert-statements#comment119632173_67682555). — Thom A, May 25 '21 at 10:18
@Larnu I do not want the first row . I want this query to be executed for each row one by one . — Ankush yadav, May 25 '21 at 10:25
But again, you're trying to reinvent the wheel here. You clearly aren't familiar with T-SQL so this is a task you should not be attempting. There are solutions out there for this already if you look for them. — Thom A, May 25 '21 at 10:25
*"@Larnu I do not want the first row "* That's not what your title says: *"Only fetching first value in column"* — Thom A, May 25 '21 at 10:25
You never did answer [why](https://stackoverflow.com/questions/67658038/need-generate-script-with-data-for-all-the-tables-in-one-go#comment119590163_67658038) you want to do this... What's wrong with the back up solutions that already exist in SQL Server? I don't doubt what we have here is an [XY Problem](http://xyproblem.info). — Thom A, May 25 '21 at 10:29
@Larnu I have mentioned below after the script . I think there is something more to be added in cursor . Please suggest . — Ankush yadav, May 25 '21 at 10:29
@Larner I have to implement this is C++ code . Basically I want to use query or procedure to generate the insert statement for all the tables . — Ankush yadav, May 25 '21 at 10:32
*"I have to implement this is C++ code"* But the above is T-SQL, *not* C++ ... — Thom A, May 25 '21 at 10:33
Yes . This particular code should be in T-SQL . C++ code is different where I can call this procedure . — Ankush yadav, May 25 '21 at 10:42
Does this answer your question? [Need generate script with data for all the tables in one go](https://stackoverflow.com/questions/67658038/need-generate-script-with-data-for-all-the-tables-in-one-go) — Dale K, May 25 '21 at 10:59
You already have an answer ^^^ with nested cursors? Asking the same question 3 times doesn't increase your odds of getting an answer. If you didn't get what you needed the first time you need to improve that question and/or ask questions of the person providing the answer. — Dale K, May 25 '21 at 10:59

prem · Answer 1 · 2021-05-25T12:08:59.753

Please try using cursor to get name, cursCol1 Should be closed at last after cursCol. FETCH NEXT is also missing for cursCol1

CREATE PROC [dbo].[usp_InsertGenerator]
AS
BEGIN
  DECLARE @tablename varchar(max)
  DECLARE @tbl 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 @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
              
              INSERT INTO @tbl (insertVal) EXEC (@query)
              -- PRINT (@query)
          END
      CLOSE cursCol
      DEALLOCATE cursCol
    FETCH NEXT FROM cursCol1 INTO @tablename
    END
    CLOSE cursCol1
    DEALLOCATE cursCol1  
    SELECT * FROM @tbl
END

Please don't use quote formatting for your own text, it should only be used for "quoting" something from somewhere else. Also you already answered this question the first time it was asked. Please don't encourage bad questions... instead refer OP back to the original question. — Dale K, May 25 '21 at 11:18
Thanks for this . But what i get now is multiple tables . For every table there is a sperate result . How can i insert of the insert statements in one temp table or just get all the results together — Ankush yadav, May 25 '21 at 11:30
I have edited the above procedure. I have added temp table '@tbl' and inserted all the insert's of the result. Please try this. Thanks — prem, May 25 '21 at 12:13

Only fetching first value in column

1 Answers1