需要一次性生成包含所有表数据的脚本。

26 浏览
0 Comments

需要一次性生成包含所有表数据的脚本。

这个问题已经在其他地方有了答案:

什么是自动生成SQL Server表的INSERT语句的最佳方法？

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

尝试一次性生成所有表中的数据脚本。

我需要对数据库中存在的所有表设置@tablename。

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 )

在执行上述过程时，它给出了以下错误。

我如何在声明相同变量后设置多个变量值。

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

admin 更改状态以发布 2023年5月19日
0

1 答案

0 Comments

在临时表中添加了另一个游标来获取“名称”

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

0