Need generate script with data for all the tables in one go

Question

CREATE PROCEDURE [dbo].[usp_InsertGenerator]
AS
BEGIN
    DECLARE @tablename varchar(100)

    CREATE TABLE #TempTable
    (
        Name varchar(max)
    )

    INSERT INTO #TempTable (Name) 
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE = 'BASE TABLE'

    SET @tablename = (SELECT name FROM #TempTable)

    DECLARE cursCol CURSOR FAST_FORWARD 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.'
        CLOSE curscol
        DEALLOCATE curscol
        RETURN
    END

    WHILE @@FETCH_STATUS = 0
    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
exec (@query)
CLOSE cursCol
DEALLOCATE cursCol
end
end
go

Trying to generate script with data for all the tables in on go. I need to set @tablename for all the tables which are present in database.

DECLARE @tablename varchar(100)
CREATE TABLE #TempTable(
Name varchar(max))
INSERT INTO #TempTable ( Name) 
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE'
set @tablename=(SELECT name from #TempTable )

When executing the above procedure , it given the below error. how can I set multiple values for any variable after declaring the same .

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, 
<, <= , >, >= or when the subquery is used as an expression.

Inserting all the data from all tables into a single table makes no sense for many reasons. What are you *really* trying to achieve here? What questions are you trying to answer? — Thom A, May 23 '21 at 09:14
I want generate script of all the tables with data in on go using procedure or query . That is the reason i use this INSERT INTO #TempTable ( Name) SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE' set @tablename=(SELECT name from #TempTable ) to get names of all the tables — Ankush yadav, May 23 '21 at 09:35
If you want to script out your database, and it's data, there are plenty of examples of how to do that's it there. Though why not use a back up? — Thom A, May 23 '21 at 10:21
I need to use Store procedure or any query to generate insert statement for all the tables in one go . — Ankush yadav, May 23 '21 at 10:52
And *why* do you need that? Again, what problem are you actually trying to solve? — Thom A, May 23 '21 at 11:15
Does this answer your question? [What is the best way to auto-generate INSERT statements for a SQL Server table?](https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table) or this one [https://stackoverflow.com/questions/6799120/script-all-data-from-sql-server-database] — Charlieface, May 23 '21 at 12:21

score 0 · Answer 1 · edited May 25 '21 at 11:00

Added another cursor to fetch 'name' from Temptable

CREATE PROC [dbo].[usp_InsertGenerator]
AS
BEGIN
    DECLARE @tablename varchar(100)
    CREATE TABLE #TempTable(Name varchar(max))
    INSERT INTO #TempTable ( Name) 
        SELECT name from sys.tables 

    -- SET @tablename=(SELECT name from #TempTable )

    DECLARE Cur_Temp CURSOR FOR SELECT name FROM #TempTable
    OPEN Cur_Temp
    FETCH NEXT FROM Cur_Temp INTO @tableName
    WHILE @@FETCH_STATUS=0
    BEGIN
        DECLARE cursCol CURSOR FAST_FORWARD 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.'
                CLOSE curscol
                DEALLOCATE curscol
                return
            END
        WHILE @@FETCH_STATUS=0
        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
                exec (@query)
            CLOSE cursCol
            DEALLOCATE cursCol
        END
    FETCH NEXT FROM Cur_Temp INTO @tableName
    END
    CLOSE Cur_Temp
    DEALLOCATE Cur_Temp
END

Need generate script with data for all the tables in one go

1 Answers1

Linked