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

时间:2020-03-17 14:02来源:计算机教程
新建一个查询窗口,然后粘贴 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, --

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

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] 

导出来的insert脚本

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

我这里演示一下怎麽用

其实SQLServer的技巧有很多

InsertGenerator 'customer', 'city=3' 

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

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

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2') 
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 
InsertGenerator 'customer', null 

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

datetime类型会有一些问题

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) 

2、根据查询条件导数据

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

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

1、导全表数据

-- Author: 桦仔-- Blog:  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脚本

点击一下,选择全部

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

有两种方式

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

--非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 INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('1','nihao',convert(money,'8.80'),convert(datetime,'02 8 2015 5:17PM')) 

补充:创建一张测试表


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

群里的人共享的另一个脚本

表结构

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

测试

或者

这个脚本有一个缺陷

这里有一个存储过程

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

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

编辑:计算机教程 本文来源:将表里的数据批量生成INSERT语句的存储过程 增强

关键词: