如何发布网络小说,穿越小说完本,古风

有時候，我們需要將某個表里的數(shù)據(jù)全部或者根據(jù)查詢條件導(dǎo)出來，遷移到另一個相同結(jié)構(gòu)的庫中

目前SQL Server里面是沒有相關(guān)的工具根據(jù)查詢條件來生成INSERT語句的，只有借助第三方工具（third party tools）

這種腳本網(wǎng)上也有很多，但是網(wǎng)上的腳本還是欠缺一些規(guī)范和功能，例如：我只想導(dǎo)出特定查詢條件的數(shù)據(jù)，網(wǎng)上的腳本都是導(dǎo)出全表數(shù)據(jù)

如果表很大，對性能會有很大影響

這里有一個存儲過程（適用于SQLServer2005 或以上版本）

-- Author: <樺仔>-- Blog: <http://www.cnblogs.com/lyhabc/>-- Create date: <//>-- 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 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腳本

比如我現(xiàn)在有三個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)

在執(zhí)行腳本的時候他只會生成dbo這個schema下面的表insert腳本

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

這個腳本有一個缺陷

無論你的表的字段是什麼數(shù)據(jù)類型，導(dǎo)出來的時候只能是字符

表結(jié)構(gòu)

CREATE TABLE [dbo].[customer](city int,region int)

導(dǎo)出來的insert腳本

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

我這里演示一下怎麼用

有兩種方式

1、導(dǎo)全表數(shù)據(jù)

InsertGenerator 'customer', null

或

InsertGenerator 'customer', ' '