自由导入你的增量数据-根据条件将sqlserver表批量生成INSERT语句的存储过程实施笔记

作者：发布时间：2020-04-05 来源:佚名点击数：

文章标题: 自由导入你的增量数据-根据条件将sqlserver表批量生成INSERT语句的存储过程增强版
关键字 : mssql-scripter,SQL Server
文章分类: 技术分享

创建时间: 2020年4月5日

                                              _.-"\
                                        _.-"     \
                                      ,-"           \
                                      \            \
                                      \ \Zoomla逐浪CMS\
                                      \ \  web开发秘笈\ \
                                        \ \  z01.com _.-;
                                        \ \    _.-"    :
                                          \ \,-"   _.-"
                                          \(   _.-"
                                            `--"

为快速灵敏开发而生
创建一个智能存储过程。
特征介绍
使用方法
- 1、导全表数据
- 2、根据查询条件导数据
在逐浪CMS中集成使用
支持SQL2000
群里人分享的另一个实例

为快速灵敏开发而生

软件开发中，常常会产生数据，数据的还原备份与迁移，是一个很重要的话题。
对于小型开发，往往都是采用全库覆盖。
对于百万、千万甚至更大的数据库，这样操作就显然用时很久，而且会影响主场景使用。
这个时候，我们需要将某个表里的数据全部或者根据查询条件导出来，迁移到另一个相同结构的库中

目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的，默认的sqlserver manager工具只能生成完整的insert语句，只有借助第三方工具（third party tools）来实现。

事实上，我们可以写一个存储过程脚本来实现它。

创建一个智能存储过程。

这种脚本网上也有很多，但是网上的脚本还是欠缺一些规范和功能，例如：我只想导出特定查询条件的数据，网上的脚本都是导出全表数据

如果表很大，对性能会有很大影响。

完整的存储过程创建语法（适合sql server 2005以上的版本）：

-- Author:      <Zoomla!逐浪CMS高端门户团队>
-- Blog:        <http://www.z01.com/blog>
-- Create date: <2020/04/05>
-- Description: <根据查询条件导出表数据的insert脚本>
-- =============================================
CREATE  PROCEDURE [dbo].[ZL_InsertGenerator]
    (
      @tableName NVARCHAR(MAX),
      @whereClause NVARCHAR(MAX)
    )
AS 

--Then it includes a cursor to fetch column specific information (column name and the data type thereof) 
--from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses 
--of an INSERT DML statement.

    DECLARE @string NVARCHAR(MAX) --for storing the first half of INSERT statement
    DECLARE @stringData NVARCHAR(MAX) --for storing the data (VALUES) related statement
    DECLARE @dataType NVARCHAR(MAX) --data types returned for respective columns
    DECLARE @schemaName NVARCHAR(MAX) --schema name returned from sys.schemas
    DECLARE @schemaNameCount int--shema count
    DECLARE @QueryString  NVARCHAR(MAX) -- provide for the whole query, 

    set @QueryString=' '

     --如果有多个schema，选择其中一个schema
    SELECT @schemaNameCount=COUNT(*)
    FROM    sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE   t.name = @tableName

    WHILE(@schemaNameCount>0)
    BEGIN

    --如果有多个schema，依次指定
    select @schemaName = name 
    from 
    (
        SELECT ROW_NUMBER() over(order by  s.schema_id) RowID,s.name
        FROM    sys.tables t
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE   t.name =  @tableName
    ) as v
    where RowID=@schemaNameCount

    --Declare a cursor to retrieve column specific information 
    --for the specified table
    DECLARE cursCol CURSOR FAST_FORWARD
    FOR
        SELECT  column_name ,
                data_type
        FROM    information_schema.columns
        WHERE   table_name = @tableName
                AND table_schema = @schemaName

    OPEN cursCol
    SET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + ']('
    SET @stringData = ''

    DECLARE @colName NVARCHAR(500)

    FETCH NEXT FROM cursCol INTO @colName, @dataType

    PRINT @schemaName
    PRINT @colName
    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
                IF @dataType IN ( 'text', 'ntext' ) --if the datatype 
                                 --is text or something else 
                    BEGIN
                        SET @stringData = @stringData + '''''''''+
          isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'
                    END
                ELSE
                    IF @dataType = 'money' --because money doesn't get converted 
                       --from varchar implicitly
                        BEGIN
                            SET @stringData = @stringData
                                + '''convert(money,''''''+
        isnull(cast(' + @colName
                                + ' as nvarchar(max)),''0.0000'')+''''''),''+'
                        END
                    ELSE
                        IF @dataType = 'datetime'
                            BEGIN
                                SET @stringData = @stringData
                                    + '''convert(datetime,''''''+
        isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+''''''),''+'
                            END
                        ELSE
                            IF @dataType = 'image'
                                BEGIN
                                    SET @stringData = @stringData + '''''''''+
       isnull(cast(convert(varbinary,' + @colName + ') 
       as varchar(6)),''0'')+'''''',''+'
                                END
                            ELSE --presuming the data type is int,bit,numeric,decimal 
                            BEGIN
                                    SET @stringData = @stringData + '''''''''+
          isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+'''''',''+'
                                END

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

            FETCH NEXT FROM cursCol INTO @colName, @dataType
        END
--After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.

    DECLARE @Query NVARCHAR(MAX) -- provide for the whole query, 
                              -- you may increase the size
    PRINT @whereClause
    IF ( @whereClause IS NOT NULL
         AND @whereClause <> ''
       )
        BEGIN  
            SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))
                + ') VALUES(''+ ' + SUBSTRING(@stringData, 0,
                                              LEN(@stringData) - 2)
                + '''+'')'' 
   FROM ' +@schemaName+'.'+ @tableName + ' WHERE ' + @whereClause
            PRINT @query
           -- EXEC sp_executesql @query --load and run the built query
--Eventually, close and de-allocate the cursor created for columns information.
        END
    ELSE
  BEGIN 
            SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))
                + ') VALUES(''+ ' + SUBSTRING(@stringData, 0,
                                              LEN(@stringData) - 2)
                + '''+'')'' 
    FROM ' + @schemaName+'.'+ @tableName

        END

    CLOSE cursCol
    DEALLOCATE cursCol

    SET @schemaNameCount=@schemaNameCount-1
    IF(@schemaNameCount=0)
    BEGIN
       SET @QueryString=@QueryString+@query
    END
    ELSE
    BEGIN
        SET @QueryString=@QueryString+@query+' UNION ALL '
    END
    PRINT convert(varchar(max),@schemaNameCount)+'---'+@QueryString
    END
    EXEC sp_executesql @QueryString --load and run the built query
--Eventually, close and de-allocate the cursor created for columns information.

特征介绍

这里要声明一下，如果你有多个schema，并且每个schema下面都有同一张表，那么脚本只会生成其中一个schema下面的表insert脚本

比如我现在有三个schema，下面都有customer这个表

CREATE TABLE dbo.[customer](city int,region int)

CREATE SCHEMA test
CREATE TABLE test.[customer](city int,region int)

CREATE SCHEMA test1
CREATE TABLE test1.[customer](city int,region int)

在执行脚本的时候他只会生成dbo这个schema下面的表insert脚本

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2')

这个脚本有一个缺陷

无论你的表的字段是什麽数据类型，导出来的时候只能是字符

表结构

CREATE TABLE [dbo].[customer](city int,region int)

导出来的insert脚本:

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2')

使用方法

有两种使用方式

1、导全表数据

ZL_InsertGenerator 'customer', null

或：

ZL_InsertGenerator 'customer', ' '

2、根据查询条件导数据

ZL_InsertGenerator 'customer', 'city=3'

或者

ZL_InsertGenerator 'customer', 'city=3 and region=8'

点击一下，选择全部

然后复制

新建一个查询窗口，然后粘贴

在逐浪CMS中集成使用

其实SQLServer的技巧有很多，比如结合变量来使用，实现动态更新，如下面的脚本：

set @最近查询时间 = '2020-04-5 19:35:57.000'

set @主表最近GID=(
select top 1 GeneralID  from ZL_CommonModel left join ZL_C_image on  ZL_CommonModel.ItemID=ZL_C_image.id where 
UpDateTime<@最近查询时间 and ModelID=52 and Status=99 order by UpDateTime desc )

set @副表最近ID=(
select top 1 ID  from ZL_CommonModel left join ZL_C_image on  ZL_CommonModel.ItemID=ZL_C_image.id where
UpDateTime<@最近查询时间 and ModelID=52 and Status=99 order by UpDateTime desc)


select @最近查询时间 as 最近查询时间,@主表最近GID as 主表最近GID,@副表最近ID as 副表最近ID

第二步：将下面的两个数字换为上方的两个ID，单独查询得出语法，并配合 :

SET IDENTITY_INSERT [Table] ON 
SET IDENTITY_INSERT [Table] OFF
使用

ZL_InsertGenerator 'ZL_CommonModel','GeneralID>=9351'
go
ZL_InsertGenerator 'zl_c_image','id>=9352'

支持SQL2000

最后，大家可以看一下代码，非常简单，如果要支持SQLServer2000，只要改一下代码就可以了（虽然sql2000使用者不多，但可以从中学到相应处理思路）。

补充：创建一张测试表

CREATE TABLE testinsert (id INT,name VARCHAR(100),cash MONEY,dtime DATETIME)


INSERT INTO [dbo].[testinsert]
        ( [id], [name], [cash], [dtime] )
VALUES  ( 1, -- id - int
          'nihao', -- name - varchar(100)
          8.8, -- cash - money
          GETDATE()  -- dtime - datetime
          )

SELECT * FROM [dbo].[testinsert]

测试

InsertGenerator 'testinsert' ,''

InsertGenerator 'testinsert' ,'name=''nihao'''

InsertGenerator 'testinsert' ,'name=''nihao'' and cash=8.8'

datetime类型会有一些问题

生成的结果会自动帮你转换

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('1','nihao',convert(money,'8.80'),convert(datetime,'02  8 2015  5:17PM'))

群里人分享的另一个实例

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL 
DROP PROC spGenInsertSQL
GO
CREATE   proc spGenInsertSQL (@tablename varchar(256),@number BIGINT,@whereClause NVARCHAR(MAX))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
  from
      (select case
                when xtype in (48,52,56,59,60,62,104,106,108,122,127)       

                     then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

                when xtype in (58,61,40,41,42)

                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

               when xtype in (167)

                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

                when xtype in (231)

                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

                when xtype in (175)

                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

                when xtype in (239)

                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

                else '''NULL'''

              end as Cols,name

         from syscolumns 

        where id = object_id(@tablename)

      ) T
IF (@number!=0 AND @number IS NOT NULL)
BEGIN
set @sql ='select top '+ CAST(@number AS VARCHAR(6000))+' ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
END
ELSE
BEGIN 
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
END


PRINT @whereClause
IF ( @whereClause IS NOT NULL  AND @whereClause <> '')
BEGIN
set @sql =@sql+' where '+@whereClause
print @sql
END

exec (@sql)
end
GO

调用示例

--非dbo默认架构需注意
--支持数据类型 ：bigint，int， bit，char，datetime，date，time，decimal，money， nvarchar(50)，tinyint， nvarchar(max)，varchar(max)，datetime2
--调用示例 如果top行或者where条件为空，只需要把参数填上null

spGenInsertSQL 'customer' --表名
, 2 --top 行数
, 'city=3 and didian=''大连'' '  --where 条件

--导出全表 where条件为空
spGenInsertSQL 'customer' --表名
, null --top 行数
,null --where 条件

INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (1,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05  5 2015  5:58PM')
INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (2,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05  5 2015  5:58PM')