CREATE PROCEDURE P_Move
@ObjectName varchar(50), --传入查询表名
@Condition varchar(2000) = '1 = 1' --传入查询的条件子句字符串
AS
--判断传入查询表名,若传入空值或空串,返回错误信息
IF (@ObjectName IS NULL) OR (LTRIM(RTRIM(@ObjectName)) = '')
RETURN -10008 --入参无效
IF (RTRIM(@Condition) IS NULL) OR (LTRIM(RTRIM(@Condition)) = '')
SELECT @Condition = '1 = 1'
DECLARE @sSql nvarchar(4000)
--取得数据集合
SET @sSql =''
SET @sSql = 'SELECT Top 1 * FROM ' + @ObjectName + ' WHERE ' + @Condition IF LEN(RTRIM(LTRIM(@sSql)))<>0
BEGIN TRAN
SET @sSql='SELECT * INTO ' + @ObjectName + ' FROM OpenRowSet(''MSDASQL'',''Driver={SQL SERVER};Server=Server;UID=Sa;Pwd=;Database=dbname'','''+ @sSql +''')'
PRINT @sSql
EXEC (@sSql)
COMMIT TRAN IF @@ERROR <> 0
GOTO ErrorHandle
ErrorHandle:
ROLLBACK TRAN
RETURN -1
GO
@ObjectName varchar(50), --传入查询表名
@Condition varchar(2000) = '1 = 1' --传入查询的条件子句字符串
AS
--判断传入查询表名,若传入空值或空串,返回错误信息
IF (@ObjectName IS NULL) OR (LTRIM(RTRIM(@ObjectName)) = '')
RETURN -10008 --入参无效
IF (RTRIM(@Condition) IS NULL) OR (LTRIM(RTRIM(@Condition)) = '')
SELECT @Condition = '1 = 1'
DECLARE @sSql nvarchar(4000)
--取得数据集合
SET @sSql =''
SET @sSql = 'SELECT Top 1 * FROM ' + @ObjectName + ' WHERE ' + @Condition IF LEN(RTRIM(LTRIM(@sSql)))<>0
BEGIN TRAN
SET @sSql='SELECT * INTO ' + @ObjectName + ' FROM OpenRowSet(''MSDASQL'',''Driver={SQL SERVER};Server=Server;UID=Sa;Pwd=;Database=dbname'','''+ @sSql +''')'
PRINT @sSql
EXEC (@sSql)
COMMIT TRAN IF @@ERROR <> 0
GOTO ErrorHandle
ErrorHandle:
ROLLBACK TRAN
RETURN -1
GO
对于大量的数据比如上百万条数据非常不适合。建议使用bcp命令。使用bcp命令时要结合实际,因为bcp命令有写log和不写log两种情况。
关于bcp使用的资料网上很多,该命令参数极多,但是功能强大。可好好研究研究。
假如用OleDbCommand该怎样写?谢谢