--=============================================--Author: --Blog: --Create date: <2014/10/18>--Description: --=============================================
CREATE PROCEDURE [InsertGenerator](@tableName NVARCHAR(100),--the table name
@whereClause NVARCHAR(MAX),--col1=1
@includeIdentity INT --include identity column(1:yes,0:no)
)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(20) --data types returned for respective columns
DECLARE @schemaName NVARCHAR(20) --schema name returned from sys.schemas
DECLARE @schemaNameCount int--shema count
DECLARE @QueryString NVARCHAR(MAX) --provide for the whole query,
DECLARE @identity INT --identity column(1:yes,0:no)
set @QueryString=' '
--如果有多个schema,选择其中一个schema
SELECT @schemaNameCount=COUNT(*)FROMsys.tables tINNER JOIN sys.schemas s ON t.schema_id =s.schema_idWHERE t.name = @tableName
WHILE(@schemaNameCount>0)BEGIN
--如果有多个schema,依次指定
select @schemaName =namefrom(SELECT ROW_NUMBER() over(order bys.schema_id) RowID,s.nameFROMsys.tables tINNER JOIN sys.schemas s ON t.schema_id =s.schema_idWHERE t.name = @tableName)asvwhere RowID=@schemaNameCount
--Declare a cursor to retrieve column specific information
--for the specified table
DECLARE cursCol CURSORFAST_FORWARDFOR
SELECTclmns.[name] AS [column_name],
usrt.[name] AS [data_type],CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity]
FROM dbo.sysobjects AS tbl WITH(NOLOCK)INNER JOIN dbo.syscolumns AS clmns WITH (NOLOCK) ON clmns.id=tbl.idLEFT JOIN dbo.systypes AS usrt WITH (NOLOCK) ON usrt.xusertype =clmns.xusertypeLEFT JOIN dbo.sysusers AS sclmns WITH (NOLOCK) ON sclmns.uid =usrt.uidLEFT JOIN dbo.systypes AS baset WITH (NOLOCK) ON baset.xusertype = clmns.xtype and baset.xusertype =baset.xtypeLEFT JOIN dbo.syscomments AS defaults WITH (NOLOCK) ON defaults.id =clmns.cdefaultLEFT JOIN dbo.syscomments AS cdef WITH (NOLOCK) ON cdef.id = clmns.id AND cdef.number =clmns.colidWHERE (tbl.[type] = 'U') AND (tbl.[name]=@tableName AND SCHEMA_NAME(tbl.uid)=@schemaName)AND CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int)=@includeIdentity
ORDER BY tbl.[name], clmns.colorderOPENcursColSET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + ']('
SET @stringData = ''
DECLARE @colName NVARCHAR(500)FETCH NEXT FROM cursCol INTO @colName, @dataType,@identity
PRINT @schemaName
PRINT @colName
IF @@fetch_status <> 0
BEGIN
PRINT 'Table' + @tableName + 'not found, processing skipped.'
CLOSEcurscolDEALLOCATEcurscolRETURN
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)),''null'')+''''''),''+'
SET @stringData = @stringData+ 'COALESCE(''''''''+CONVERT(varchar(max),' + @colName + ',120)+'''''''',''NULL'')+'',''+'
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,@identity
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
PRINT 'stringData:'+@stringData
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
CLOSEcursColDEALLOCATEcursColSET @schemaNameCount=@schemaNameCount-1
IF(@schemaNameCount=0)BEGIN
SET @QueryString=@QueryString+@query
END
ELSE
BEGIN
SET @QueryString=@QueryString+@query+'UNION ALL'
END
--SET @QueryString=REPLACE(@QueryString,'convert(datetime,''NULL'')',NULL)
PRINT CONVERT(VARCHAR(MAX),@schemaNameCount)+'---'+@QueryString
END
--PRINT @QueryString
EXEC sp_executesql @QueryString --load and run the built query--Eventually, close and de-allocate the cursor created for columns information.