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

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

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

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

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

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

-- Author: <桦仔>-- Blog: <http://www.cnblogs.com/lyhabc/>-- Create date: <//>-- 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 statementDECLARE @stringData NVARCHAR（MAX） --for storing the data （VALUES） related statementDECLARE @dataType NVARCHAR（MAX） --data types returned for respective columnsDECLARE @schemaName NVARCHAR（MAX） --schema name returned from sys.schemasDECLARE @schemaNameCount int--shema countDECLARE @QueryString NVARCHAR（MAX） -- provide for the whole query, set @QueryString=" "--如果有多个schema，选择其中一个schemaSELECT @schemaNameCount=COUNT（*）FROM sys.tables tINNER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.name = @tableNameWHILE（@schemaNameCount>）BEGIN--如果有多个schema，依次指定select @schemaName = name from （SELECT ROW_NUMBER（） over（order by s.schema_id） RowID,s.nameFROM sys.tables tINNER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.name = @tableName） as vwhere RowID=@schemaNameCount--Declare a cursor to retrieve column specific information --for the specified tableDECLARE cursCol CURSOR FAST_FORWARDFORSELECT column_name ,data_typeFROM information_schema.columnsWHERE table_name = @tableNameAND table_schema = @schemaNameOPEN cursColSET @string = "INSERT INTO [" + @schemaName + "].[" + @tableName + "]（"SET @stringData = ""DECLARE @colName NVARCHAR（）FETCH NEXT FROM cursCol INTO @colName, @dataTypePRINT @schemaNamePRINT @colNameIF @@fetch_status <> BEGINPRINT "Table " + @tableName + " not found, processing skipped."CLOSE curscolDEALLOCATE curscolRETURNENDWHILE @@FETCH_STATUS = BEGINIF @dataType IN （ "varchar", "char", "nchar", "nvarchar" ）BEGINSET @stringData = @stringData + """""""""+isnull（" + @colName + ",""""）+"""""",""+"ENDELSEIF @dataType IN （ "text", "ntext" ） --if the datatype --is text or something else BEGINSET @stringData = @stringData + """""""""+isnull（cast（" + @colName + " as nvarchar（max））,""""）+"""""",""+"ENDELSEIF @dataType = "money" --because money doesn"t get converted --from varchar implicitlyBEGINSET @stringData = @stringData+ """convert（money,""""""+isnull（cast（" + @colName+ " as nvarchar（max））,"".""）+""""""）,""+"ENDELSEIF @dataType = "datetime"BEGINSET @stringData = @stringData+ """convert（datetime,""""""+isnull（cast（" + @colName + " as nvarchar（max））,""""）+""""""）,""+"ENDELSEIF @dataType = "image"BEGINSET @stringData = @stringData + """""""""+isnull（cast（convert（varbinary," + @colName + "） as varchar（））,""""）+"""""",""+"ENDELSE --presuming the data type is int,bit,numeric,decimal BEGINSET @stringData = @stringData + """""""""+isnull（cast（" + @colName + " as nvarchar（max））,""""）+"""""",""+"ENDSET @string = @string + "[" + @colName + "]" + ","FETCH NEXT FROM cursCol INTO @colName, @dataTypeEND--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 sizePRINT @whereClauseIF （ @whereClause IS NOT NULLAND @whereClause <> ""）BEGIN SET @query = "SELECT """ + SUBSTRING（@string, , LEN（@string））+ "） VALUES（""+ " + SUBSTRING（@stringData, ,LEN（@stringData） - ）+ """+""）"" FROM " +@schemaName+"."+ @tableName + " WHERE " + @whereClausePRINT @query-- EXEC sp_executesql @query --load and run the built query--Eventually, close and de-allocate the cursor created for columns information.ENDELSEBEGIN SET @query = "SELECT """ + SUBSTRING（@string, , LEN（@string））+ "） VALUES（""+ " + SUBSTRING（@stringData, ,LEN（@stringData） - ）+ """+""）"" FROM " + @schemaName+"."+ @tableNameENDCLOSE cursColDEALLOCATE cursColSET @schemaNameCount=@schemaNameCount-IF（@schemaNameCount=）BEGINSET @QueryString=@QueryString+@queryENDELSEBEGINSET @QueryString=@QueryString+@query+" UNION ALL "ENDPRINT convert（varchar（max）,@schemaNameCount）+"---"+@QueryStringENDEXEC 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 testCREATE TABLE test.[customer]（city int,region int）CREATE SCHEMA test1CREATE 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 - moneyGETDATE（） -- 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 spGenInsertSQLGOCREATE proc spGenInsertSQL （@tablename varchar（256）,@number BIGINT,@whereClause NVARCHAR（MAX））asbegindeclare @sql varchar（8000）declare @sqlValues varchar（8000）set @sql =" （"set @sqlValues = "values （""+"select @sqlValues = @sqlValues + cols + " + "","" + " ,@sql = @sql + "[" + name + "],"from（select casewhen 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,namefrom syscolumns where id = object_id（@tablename）） TIF （@number！=0 AND @number IS NOT NULL）BEGINset @sql ="select top "+ CAST（@number AS VARCHAR（6000））+" ""INSERT INTO ["+ @tablename + "]" + left（@sql,len（@sql）-1）+"） " + left（@sqlValues,len（@sqlValues）-4） + "）"" from "+@tablenameprint @sqlENDELSEBEGIN set @sql ="select ""INSERT INTO ["+ @tablename + "]" + left（@sql,len（@sql）-1）+"） " + left（@sqlValues,len（@sqlValues）-4） + "）"" from "+@tablenameprint @sqlENDPRINT @whereClauseIF （ @whereClause IS NOT NULL AND @whereClause <> ""）BEGINset @sql =@sql+" where "+@whereClauseprint @sqlENDexec （@sql）endGO

调用示例

--非dbo默认架构需注意--支持数据类型 ：bigint，int， bit，char，datetime，date，time，decimal，money， nvarchar（50），tinyint， nvarchar（max），varchar（max），datetime2--调用示例 如果top行或者where条件为空，只需要把参数填上nullspGenInsertSQL "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"）

以上所述是本文给大家分享的将表里的数据批量生成INSERT语句的存储过程增强版，希望大家喜欢。

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

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