將表里的數(shù)據(jù)批量生成INSERT語(yǔ)句的存儲(chǔ)過(guò)程 增強(qiáng)版
有時(shí)候,我們需要將某個(gè)表里的數(shù)據(jù)全部或者根據(jù)查詢條件導(dǎo)出來(lái),遷移到另一個(gè)相同結(jié)構(gòu)的庫(kù)中
目前SQL Server里面是沒(méi)有相關(guān)的工具根據(jù)查詢條件來(lái)生成INSERT語(yǔ)句的,只有借助第三方工具(third party tools)
這種腳本網(wǎng)上也有很多,但是網(wǎng)上的腳本還是欠缺一些規(guī)范和功能,例如:我只想導(dǎo)出特定查詢條件的數(shù)據(jù),網(wǎng)上的腳本都是導(dǎo)出全表數(shù)據(jù)
如果表很大,對(duì)性能會(huì)有很大影響
?
這里有一個(gè)存儲(chǔ)過(guò)程( 適用于SQLServer2005 或以上版本 )
-- Author: <樺仔> -- Blog: <http://www.cnblogs.com/lyhabc/> -- Create date: <2014/10/18> -- Description: <根據(jù)查詢條件導(dǎo)出表數(shù)據(jù)的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 = ' ' -- 如果有多個(gè)schema,選擇其中一個(gè)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 -- 如果有多個(gè)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.
?
這里要聲明一下,如果你有多個(gè)schema,并且每個(gè)schema下面都有同一張表,那么腳本只會(huì)生成其中一個(gè)schema下面的表insert腳本
比如我現(xiàn)在有三個(gè)schema,下面都有customer這個(gè)表
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 )
在執(zhí)行腳本的時(shí)候他只會(huì)生成dbo這個(gè)schema下面的表insert腳本
INSERT INTO [ dbo ] . [ customer ] ( [ city ] , [ region ] ) VALUES ( ' 1 ' , ' 2 ' )
?
這個(gè)腳本有一個(gè)缺陷
無(wú)論你的表的字段是什麼數(shù)據(jù)類型,導(dǎo)出來(lái)的時(shí)候只能是字符
表結(jié)構(gòu)
CREATE TABLE [ dbo ] . [ customer ] (city int ,region int )
?
導(dǎo)出來(lái)的insert腳本
INSERT INTO [ dbo ] . [ customer ] ( [ city ] , [ region ] ) VALUES ( ' 1 ' , ' 2 ' )
?
?
我這里演示一下怎麼用
有兩種方式
1、導(dǎo)全表數(shù)據(jù)
InsertGenerator ' customer ' , null
或
InsertGenerator ' customer ' , ' '
?
2、根據(jù)查詢條件導(dǎo)數(shù)據(jù)
InsertGenerator ' customer ' , ' city=3 '
或者
InsertGenerator ' customer ' , ' city=3 and region=8 '
點(diǎn)擊一下,選擇全部
然后復(fù)制
新建一個(gè)查詢窗口,然后粘貼
?
其實(shí)SQLServer的技巧有很多
最后,大家可以看一下代碼,非常簡(jiǎn)單,如果要支持SQLServer2000,只要改一下代碼就可以了
?
補(bǔ)充:創(chuàng)建一張測(cè)試表
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 ]
測(cè)試
InsertGenerator ' testinsert ' , '' InsertGenerator ' testinsert ' , ' name= '' nihao ''' InsertGenerator ' testinsert ' , ' name= '' nihao '' and cash=8.8 '
datetime類型會(huì)有一些問(wèn)題
?
生成的結(jié)果會(huì)自動(dòng)幫你轉(zhuǎn)換
INSERT INTO [ dbo ] . [ testinsert ] ( [ id ] , [ name ] , [ cash ] , [ dtime ] ) VALUES ( ' 1 ' , ' nihao ' , convert ( money , ' 8.80 ' ), convert ( datetime , ' 02 8 2015 5:17PM ' ))
?
?
如有不對(duì)的地方,歡迎大家拍磚o(∩_∩)o?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
