--處理方法 use A go --新增 insert into B.dbo.B1 selet * from A1--清空 truncate table A1
想问问,你插入B库B1表后,删除是对A库表而言还是B库的表。
我对sql不是很熟悉,望各位能帮我说的详细点。或都帮我写一下。谢谢了
插入B库b1表后,删除A库a1表中的
if object_id('[TBA]') is not null drop table [TBA] go create table [TBA] (id int,name nvarchar(4)) insert into [TBA] select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd' union all select 5,'ff' --> Title : Generating test data [TBB] --> Author : --> Date : 2011-07-07 14:07:36 if object_id('[TBB]') is not null drop table [TBB] go create table [TBB] (id sql_variant,name sql_variant) insert into [TBB] select * from [TBA] select * from [TBB] DELETE FROM TBA OUTPUT DELETED.* INTO TBB WHERE id = 1
use test go SET NOCOUNT ON if OBJECT_ID('dbo.tb_A','U') is not null drop table dbo.tb_A create table dbo.tb_A ( id int not null ); GO if OBJECT_ID('dbo.tb_B','U') is not null drop table dbo.tb_B create table dbo.tb_B ( id int not null ); GO INSERT INTO dbo.tb_A SELECT 1 UNION ALL SELECT 2 ; GOSELECT * FROM dbo.tb_A; GODELETE FROM dbo.tb_A OUTPUT deleted.* INTO dbo.tb_B WHERE id = 1; GOSELECT * FROM dbo.tb_A;SELECT * FROM dbo.tb_B; 结果: id ----------- 1 2id ----------- 2id ----------- 1
create table #t1(col1 varchar(10),col2 varchar(10),col3 varchar(10)) create table #t2(col1 varchar(10),col2 varchar(10),col3 varchar(10))while exists(select top 1 0 From #t1) begin delete top (100) from #t1 output deleted.* into #t2 waitfor delay '00:00:00:300' end --用循环处理
use A
go
--新增
insert into B.dbo.B1
selet * from A1--清空
truncate table A1
go
create table [TBA] (id int,name nvarchar(4))
insert into [TBA]
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd' union all
select 5,'ff'
--> Title : Generating test data [TBB]
--> Author :
--> Date : 2011-07-07 14:07:36
if object_id('[TBB]') is not null drop table [TBB]
go
create table [TBB] (id sql_variant,name sql_variant)
insert into [TBB]
select * from [TBA]
select * from [TBB]
DELETE FROM TBA
OUTPUT DELETED.* INTO TBB
WHERE id = 1
在B表B1表建觸發器時,當插入時刪除A庫A1表根據自己的情況選擇
use test
go
SET NOCOUNT ON
if OBJECT_ID('dbo.tb_A','U') is not null
drop table dbo.tb_A
create table dbo.tb_A
(
id int not null
);
GO
if OBJECT_ID('dbo.tb_B','U') is not null
drop table dbo.tb_B
create table dbo.tb_B
(
id int not null
);
GO INSERT INTO dbo.tb_A
SELECT 1
UNION ALL
SELECT 2
;
GOSELECT *
FROM dbo.tb_A;
GODELETE
FROM dbo.tb_A
OUTPUT deleted.*
INTO dbo.tb_B
WHERE id = 1;
GOSELECT *
FROM dbo.tb_A;SELECT *
FROM dbo.tb_B;
结果:
id
-----------
1
2id
-----------
2id
-----------
1
1.如果A1数据量很大,则会形成一个大的事务,如果B1在同步链上,将严重影响同步,甚至导致LogReader死掉
2.新增和清空不在一个事务中,很可能导致数据完整性问题。比如:在执行新增成功后的刹那,数据Down掉了
3.如果A1表在同步链上,不能使用TRUNCATE TABLE操纵
create table #t2(col1 varchar(10),col2 varchar(10),col3 varchar(10))while exists(select top 1 0 From #t1)
begin
delete top (100) from #t1 output deleted.* into #t2
waitfor delay '00:00:00:300'
end
--用循环处理