我们来自五湖四海,不为别的,只因有共同的爱好,为中国互联网发展出一分力!
领航时时彩重庆版

时时彩平台跑路:将表里的数据批量生成INSERT语句的存储过程 增强版

2015年06月11日01:57 阅读: 22771 次

领航时时彩重庆版 www.9nwl5.cn 标签: 将表里的数据批量生成INSERT, 语句的存储过程 增强版

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

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

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

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

 

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

-- Author:      <桦仔> -- Blog:        <//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'))

 

 

 

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

 View Code

 

 

调用示例

--非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')
分享到: 更多
©2018 安全焦点 版权所有.

  • 奚国华委员:新兴际华将党建六大优势转化为发展优势 2018-12-06
  • 创业女青年哈丽娟:世界是自己的 与他人没关系 2018-12-06
  • 移植2胚怀5胎 一针减3化风险 十堰市人民医院再创生命奇迹 2018-11-06
  • 首届妈祖信俗“立德”论坛成功举办 2018-10-25
  • 郧西男孩两条腿“长”在一起 十堰市人民医院手术整形帮他迈开双腿 2018-10-25
  • 重磅 江西省委副书记李炳军同志任省委党校校长 2018-10-19
  • 商务部新闻发言人对美白宫6月18日声明发表谈话 2018-10-12
  • 从献血条件,看看你的身体是否达标 2018-09-26
  • 全国“非遗”保护工作先进名单公布 2018-09-26
  • 储大同 中国医学科学院肿瘤医院首席科学家 2018-08-15
  • 【理上网来·喜迎十九大】扶贫也创新!十八大以来年均减贫人数超1300万 获历史突破 2018-08-15
  • 开创美丽中国建设新局面 2018-08-13
  • 毛泽东三游故宫看了些什么 2018-08-13
  • 独家视频:新时代来啦! 2018-08-12
  • 旅游市场再创佳绩端午假期河北接待游客2026.77万 揽金147.79亿 2018-08-12
  • 684| 304| 752| 87| 999| 667| 26| 752| 144| 790|