將內外的數據批量生成INSERT語句的存儲進程 加強版。本站提示廣大學習愛好者:(將內外的數據批量生成INSERT語句的存儲進程 加強版)文章只能為提供參考,不一定能成為您想要的結果。以下是將內外的數據批量生成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 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>) 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() FETCH NEXT FROM cursCol INTO @colName, @dataType PRINT @schemaName PRINT @colName IF @@fetch_status <> BEGIN PRINT 'Table ' + @tableName + ' not found, processing skipped.' CLOSE curscol DEALLOCATE curscol RETURN END WHILE @@FETCH_STATUS = 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)),''.'')+''''''),''+' END ELSE IF @dataType = 'datetime' BEGIN SET @stringData = @stringData + '''convert(datetime,''''''+ isnull(cast(' + @colName + ' as nvarchar(max)),'''')+''''''),''+' END ELSE IF @dataType = 'image' BEGIN SET @stringData = @stringData + '''''''''+ isnull(cast(convert(varbinary,' + @colName + ') as varchar()),'''')+'''''',''+' END ELSE --presuming the data type is int,bit,numeric,decimal BEGIN SET @stringData = @stringData + '''''''''+ isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+' 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, , LEN(@string)) + ') VALUES(''+ ' + SUBSTRING(@stringData, , LEN(@stringData) - ) + '''+'')'' 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, , LEN(@string)) + ') VALUES(''+ ' + SUBSTRING(@stringData, , LEN(@stringData) - ) + '''+'')'' FROM ' + @schemaName+'.'+ @tableName END CLOSE cursCol DEALLOCATE cursCol SET @schemaNameCount=@schemaNameCount- IF(@schemaNameCount=) 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'))
--------------------------------------------------------------------------------
群裡的人同享的另外一個劇本
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')
以上所述是本文給年夜家分享的將內外的數據批量生成INSERT語句的存儲進程 加強版,願望年夜家愛好。