mysql把表里的数据导出insert_sql server 数据库导出表里所有数据成 insert 语句

--=============================================--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.