将表里的数据批量生成INSERT语句的存储过程 增强版

简介: 原文:将表里的数据批量生成INSERT语句的存储过程 增强版将表里的数据批量生成INSERT语句的存储过程 增强版 有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中 目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助...
原文: 将表里的数据批量生成INSERT语句的存储过程 增强版

将表里的数据批量生成INSERT语句的存储过程 增强版

有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中

目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助第三方工具(third party tools)

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

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

 

这里有一个存储过程(适用于SQLServer2005 或以上版本

-- Author:      <桦仔>
-- Blog:        <http://www.cnblogs.com/lyhabc/>
-- Create date: <2014/10/18>
-- Description: <根据查询条件导出表数据的insert脚本>
-- =============================================
CREATE  PROCEDURE 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、导全表数据

InsertGenerator 'customer', null

InsertGenerator 'customer', ' '

 

2、根据查询条件导数据

InsertGenerator 'customer', 'city=3'

或者

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

点击一下,选择全部

然后复制

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

 

其实SQLServer的技巧有很多

最后,大家可以看一下代码,非常简单,如果要支持SQLServer2000,只要改一下代码就可以了

 

补充:创建一张测试表

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'))

 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
存储 SQL
将表里的数据批量生成INSERT语句的存储过程 继续增强版
原文:将表里的数据批量生成INSERT语句的存储过程 继续增强版 文章继续 桦仔兄的文章 将表里的数据批量生成INSERT语句的存储过程 增强版 继续增强... 本来打算将该内容回复于桦仔兄的文章的下面的,但是不知为何博客园就是不让提交!.... 所以在这里贴出来吧,算作继续增加文章中解决的:根据查询条件自动生成插入脚本的需求,其实这种需求还是蛮常见的。
996 0
|
11月前
|
SQL Oracle 关系型数据库
【SQL开发实战技巧】系列(八):聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
插入数据、阻止对某几列插入的实现、复制表的定义和数据以及注意事项、比创建约束功能更强大!用 WITH CHECK OPTION限制数据录入、如何一个insert将数据同时插入多个表【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。但是!ORA - 01723 : 不允许长度为 0 的列。
【SQL开发实战技巧】系列(八):聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
|
存储 SQL 缓存
MYSQL查询和插入数据的流程是怎样的
MYSQL查询和插入数据的流程是怎样的
MYSQL查询和插入数据的流程是怎样的
|
SQL 数据库 HIVE
HIVE表 DML 操作——第4关:将 select 查询结果写入文件
HIVE表 DML 操作——第4关:将 select 查询结果写入文件
203 0
|
数据库 Go 关系型数据库
常见数据库触发器查询与删除语句
常见数据库元数据查询方法
1879 0
|
SQL Go 数据库
使用一个T-SQL语句批量查询数据表占用空间及其行数
原文: 使用一个T-SQL语句批量查询数据表占用空间及其行数   要找到数据库中数据表占用的空间和存在的行数。可以使用sp_spaceused搭配数据表的名称。
1073 0
|
存储
基于存储过程实现批量复制表(Procedure)的方案实现
基于存储过程实现批量复制表(Procedure)的方案实现
561 0
|
SQL 存储
SQL查询表和存储过程创建修改日期
  查询建立时间 --表select * from sysobjects where id=object_id(N'表名') and xtype='U'  --表的结构   select * from syscolumns where id=object_id(N'表名')     --存储过程...
780 0