-- Author:      <桦仔>
-- Blog:        <http://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 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>0)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(500)FETCH NEXT FROM cursCol INTO @colName, @dataTypePRINT @schemaNamePRINT @colNameIF @@fetch_status <> 0BEGINPRINT 'Table ' + @tableName + ' not found, processing skipped.'CLOSE curscolDEALLOCATE curscolRETURNENDWHILE @@FETCH_STATUS = 0BEGINIF @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)),''0.0000'')+''''''),''+'ENDELSEIF @dataType = 'datetime'BEGINSET @stringData = @stringData+ '''convert(datetime,''''''+isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+''''''),''+'ENDELSEIF @dataType = 'image'BEGINSET @stringData = @stringData + '''''''''+isnull(cast(convert(varbinary,' + @colName + ') as varchar(6)),''0'')+'''''',''+'ENDELSE --presuming the data type is int,bit,numeric,decimal BEGINSET @stringData = @stringData + '''''''''+isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+'''''',''+'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, 0, LEN(@string))+ ') VALUES(''+ ' + SUBSTRING(@stringData, 0,LEN(@stringData) - 2)+ '''+'')'' 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, 0, LEN(@string))+ ') VALUES(''+ ' + SUBSTRING(@stringData, 0,LEN(@stringData) - 2)+ '''+'')'' FROM ' + @schemaName+'.'+ @tableNameENDCLOSE cursColDEALLOCATE cursColSET @schemaNameCount=@schemaNameCount-1IF(@schemaNameCount=0)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.

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)

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

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

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

InsertGenerator 'customer', null

InsertGenerator 'customer', ' '

InsertGenerator 'customer', 'city=3'

InsertGenerator 'customer', 'city=3 and region=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]

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

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('1','nihao',convert(money,'8.80'),convert(datetime,'02  8 2015  5:17PM'))