源数据库:SQL SERVER2005
目的数据库:mysql5.5
数据级别:千万级以上/表要求:
1000000条数据生成一个insert脚本到一个.sql文件;
用存储过程实现这是我导出.sql文件的存储过程,想要每1000000条数据生成一个文件,需要怎么修改?
USE [data]
GO
/****** 对象: StoredProcedure [dbo].[explordata] 脚本日期: 08/11/2012 14:56:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[explordata]
@tableName Varchar(100)
AS
DECLARE @IsIdentity INT
DECLARE @columnName VARCHAR(100)
DECLARE @TypeName VARCHAR(100)
DECLARE @columns VARCHAR(8000)
DECLARE @columnsAndhead VARCHAR(8000)
SET @columnsAndhead = 'INSERT INTO '+@tableName+'('
SET @columns = ''
DECLARE a CURSOR FOR
SELECT COLUMNPROPERTY( a.id,a.name,'IsIdentity') AS IsIdentity, a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype AND b.xtype=b.xusertype
WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)
OPEN a FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsIdentity =0
BEGIN
IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
BEGIN
SET @columns = @columns + 'ISNULL(CAST('+@columnName +' AS VARCHAR),''NULL'')+'',''+'
END
IF @TypeName='datetime'
BEGIN
SET @columns = @columns + 'ISNULL(CONVERT(varchar(16), '+@columnName+', 111),''NULL'')+'',''+'
END
ELSE
BEGIN
SET @columns = @columns+ '''''''''+ ISNULL(CAST('+@columnName +' AS VARCHAR(50)),''NULL'')+ ''''''''+'',''+'
END
SET @columnsAndhead = @columnsAndhead + ''+ @columnName +','
END
FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
END
SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-1) +') VALUES('''
SELECT @columns = left(@columns,len(@columns)-5) CLOSE a
DEALLOCATE a exec('select '''+@columnsAndhead+'+'+@columns +'+'');'' as InsertSQL from '+@tableName)
目的数据库:mysql5.5
数据级别:千万级以上/表要求:
1000000条数据生成一个insert脚本到一个.sql文件;
用存储过程实现这是我导出.sql文件的存储过程,想要每1000000条数据生成一个文件,需要怎么修改?
USE [data]
GO
/****** 对象: StoredProcedure [dbo].[explordata] 脚本日期: 08/11/2012 14:56:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[explordata]
@tableName Varchar(100)
AS
DECLARE @IsIdentity INT
DECLARE @columnName VARCHAR(100)
DECLARE @TypeName VARCHAR(100)
DECLARE @columns VARCHAR(8000)
DECLARE @columnsAndhead VARCHAR(8000)
SET @columnsAndhead = 'INSERT INTO '+@tableName+'('
SET @columns = ''
DECLARE a CURSOR FOR
SELECT COLUMNPROPERTY( a.id,a.name,'IsIdentity') AS IsIdentity, a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype AND b.xtype=b.xusertype
WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)
OPEN a FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsIdentity =0
BEGIN
IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
BEGIN
SET @columns = @columns + 'ISNULL(CAST('+@columnName +' AS VARCHAR),''NULL'')+'',''+'
END
IF @TypeName='datetime'
BEGIN
SET @columns = @columns + 'ISNULL(CONVERT(varchar(16), '+@columnName+', 111),''NULL'')+'',''+'
END
ELSE
BEGIN
SET @columns = @columns+ '''''''''+ ISNULL(CAST('+@columnName +' AS VARCHAR(50)),''NULL'')+ ''''''''+'',''+'
END
SET @columnsAndhead = @columnsAndhead + ''+ @columnName +','
END
FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
END
SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-1) +') VALUES('''
SELECT @columns = left(@columns,len(@columns)-5) CLOSE a
DEALLOCATE a exec('select '''+@columnsAndhead+'+'+@columns +'+'');'' as InsertSQL from '+@tableName)
解决方案 »
- 存储过程 like怎么使用阿?
- SQL排序问题
- 关于更新多行数据的存储过程该怎么写?
- 有关数据库字段匹配的问题!
- 【求助】MSSQL2005自增长字段
- 建立视图后,如果第一个表的行数是100,第二个表的行数是50,那么打开视图时,一共就显示50行(它以行数少的为标准),第一个表的100行就
- 有一条查询语句不知该怎么写(高手请进)(在线等待中)
- C#和SQL数据库连接问题
- 考试题:MIS的发展和趋势?
- 怪事:我以前一直在 win2000 Professional 装SQL Server2000 Enterprise用的挺好!
- 如何使用SSIS用post定时提交给脚本文件返回XML记录?
- 数据库服务器启动后,报错: database cannot be autostarted during server shutdown or startup.
这种效率太低了参考