定义一个存储过程,并使用游标。然后循环变理就好。 USE [myyuren] GO /****** 对象: StoredProcedure [dbo].[test4] 脚本日期: 04/17/2010 11:42:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[test4]
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. DECLARE @firstname varchar(60) DECLARE @lastname varchar(60) DECLARE @salary decimal(10,2) DECLARE @id int declare test4_cursor cursor for select id,first_name,last_name, salary from dbo.employee3 open test4_cursor fetch test4_cursor into @id,@firstname,@lastname,@salary while (@@fetch_status = 0) begin print @firstname update employee2 set first_name = @firstname,last_name=@lastname,salary=@salary WHERE id=@id FETCH NEXT FROM test4_cursor INTO @id,@firstname,@lastname,@salary end close test4_cursor /*关闭游标*/ deallocate test4_cursor END
是什么数据库? 没必要100条操作一次 如果两个表空间在一个库里的话declarebegin for i in (select * from 用户名.A表) loop insert into B表(字段1,字段2...) values (i.字段1||i.字段2||i.字段3,i.第二列); commit; end loop; end;如果两个库是远程的话,可以考虑建立DBlink
有多少数据啊?需要一条条做?
USE [myyuren]
GO
/****** 对象: StoredProcedure [dbo].[test4] 脚本日期: 04/17/2010 11:42:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[test4]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @firstname varchar(60)
DECLARE @lastname varchar(60)
DECLARE @salary decimal(10,2)
DECLARE @id int
declare test4_cursor cursor for
select id,first_name,last_name, salary from dbo.employee3
open test4_cursor
fetch test4_cursor into @id,@firstname,@lastname,@salary
while (@@fetch_status = 0)
begin
print @firstname
update employee2 set first_name = @firstname,last_name=@lastname,salary=@salary WHERE id=@id
FETCH NEXT FROM test4_cursor INTO @id,@firstname,@lastname,@salary
end
close test4_cursor /*关闭游标*/
deallocate test4_cursor
END
如果两个表空间在一个库里的话declarebegin
for i in (select * from 用户名.A表)
loop
insert into B表(字段1,字段2...) values (i.字段1||i.字段2||i.字段3,i.第二列);
commit;
end loop;
end;如果两个库是远程的话,可以考虑建立DBlink