34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
SELECT * FROM Table_1
SELECT * FROM Table_2
--在这里操作
SELECT * FROM Table_1
SELECT * FROM Table_2
DROP TABLE TABLE_1
DROP TABLE TABLE_2
/* 要求A表结果是:
1 Lei Chi
2 Alex Chi
*/
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
INSERT INTO Table_1 VALUES(1,'adsd','Chi1');
INSERT INTO Table_1 VALUES(2,'adsd','Chi')
INSERT INTO Table_1 VALUES(3,'Jim','Green')
INSERT INTO Table_1 VALUES(4,'44','44')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
INSERT INTO Table_2 VALUES(3,'Lei','Chi')
INSERT INTO Table_2 VALUES(9,'Fin','Chi')
SET NOCOUNT ON
SELECT * FROM Table_1
SELECT * FROM Table_2
MERGE TABLE_1 AS Target
USING (SELECT ID, Name1, Name2 FROM Table_2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET Target.Name1 = Source.Name1, Target.Name2 = Source.Name2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name1, Name2) VALUES(ID, Name1, Name2)
WHEN NOT MATCHED BY Source THEN
DELETE;
--OUTPUT $action, Inserted.*, Deleted.*;
SET NOCOUNT ON
SELECT * FROM Table_1
SELECT * FROM Table_2
SET NOCOUNT ON
DROP TABLE TABLE_1
DROP TABLE TABLE_2
--DECLARE @ID INT
--DECLARE @Name1 NCHAR(10)
--DECLARE @Name2 NCHAR(10)
--DECLARE mig CURSOR FOR
-- (SELECT * FROM Table_1)
--OPEN mig
--FETCH NEXT FROM mig INTO @ID,@Name1,@Name2
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- FETCH NEXT FROM mig INTO @ID,@Name1,@Name2
--END
--CLOSE mig
--DEALLOCATE mig
--EXEC sp_estimate_data_compression_savings 'dbo', 'Table_1', NULL, NULL, 'ROW' ;
--select * from Table_1 WHERE checksum(Name2) not in (select checksum(Name2) from Table_2) union all
--select * from Table_2 where checksum(Name2) not in (select checksum(Name2) from Table_1)
~~~顶~~~
期待继续赐教
这样你写2个存储过程
(1)当插入数据到表2的时候 判断条件之后插入表1
(2)当删除表2数据的时候,根据条件查找然后删除表1对应的信息
MERGE TABLE_1 AS Target
USING (SELECT ID, Name1, Name2 FROM Table_2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET Target.Name1 = Source.Name1, Target.Name2 = Source.Name2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name1, Name2) VALUES(ID, Name1, Name2)
WHEN NOT MATCHED BY Source THEN
DELETE
OUTPUT $action, Inserted.*, Deleted.*;
这个例子只是没有选择性的更新
不用触发器
此题无解
上面的我那个包含插入,更新,删除
修改下
完整版本
if object_id('TABLE_1') is not null drop table TABLE_1
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
--插入测试
INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1
ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi
(3 行受影响)
--删除测试
delete from Table_2 where ID=3
ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
(2 行受影响)
--更新测试
select * from Table_2
update Table_2 set Name1='ALex2' where ID=2
ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 ALex2 Chi
(2 行受影响)
if object_id('TABLE_1') is not null drop table TABLE_1
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,updated U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1
ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi
(3 行受影响)
delete from Table_2 where ID=3
ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
(2 行受影响)